SQL Like Operator with CF & Access

S

soundcompanies

Perch
I'm having problems with CF to Access and the Like Operator.

Code: 
		<cfset addEventArtistCrit = "AND ((t1.eventArtists) Like '" &  #dbWildCard# & toString(#url.artistId#) & #dbWildCard# & "')">
Code: 
	<cfquery datasource="#dsn#" name="getEvents">
		SELECT t1.EventId
			, t1.EventName
			, t1.VenueId
			, t1.eventActive
			, t1.EventDate
			, t1.PromoterId
			, t1.eventArtists
		 FROM tblEvents AS t1 
		INNER
		 JOIN tblEvents AS t2
		 ON (t1.VenueId = t2.VenueId) AND (t1.EventDate = t2.EventDate)
		WHERE (((t1.EventDate) Between #variable.ODBCstartQueryDate# And #variable.ODBCendQueryDate#)
		 AND ((t1.eventActive)<>False))
			 #addEventArtistCrit#
		GROUP
		 	BY t1.EventId
		 	, t1.EventName
		 	, t1.VenueId
		 	, t1.eventActive
		 	, t1.EventDate
			, t1.PromoterId
			, t1.eventArtists
		HAVING (((t1.EventId)=Max([t2].[eventID])));
	</cfquery>

The artistID that is passed in is numeric but the values stored are comma separated values.

If I remove the wildcards and the quotes, it will find an exact match, eg.
Code: 
Like 11
, but if I have the quotes and the wildcards I get an error, eg.
Code: 
Like "*11*"

Here is the error:
Data type mismatch in criteria expression
Click to expand...

The t1.eventArtists is stored as a string so I can't figure why I'm getting an error.

Any ideas?

Thanks,

Brent
 
peskypup said:
I'll admit that I haven't really read your post closely, but what happens if you try:

LIKE '%11%'
Click to expand...
That would be my thought as well. I know with SQL Server the % is the wildcard character, not *. I *think* this is the same in Access.
 
hatton said:
That would be my thought as well. I know with SQL Server the % is the wildcard character, not *. I *think* this is the same in Access.
Click to expand...

Access does not support % as a wildcard character. It does, in fact, support the * character, as well as a few others. From MS Access help:

* Matches any number of characters. It can be used as the first or last character in the character string. wh* finds what, white, and why
? Matches any single alphabetic character. B?ll finds ball, bell, and bill
[ ] Matches any single character within the brackets. B[ae]ll finds ball and bell but not bill
! Matches any character not in the brackets. b[!ae]ll finds bill and bull but not bell
- Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd
# Matches any single numeric character. 1#3 finds 103, 113, 123


riley
 
riley said:
Access does not support % as a wildcard character. It does, in fact, support the * character, as well as a few others.
Click to expand...

If you're actually in Access, you use the * but I think if you're using Jet to access the Access DB, then you have to use %. I think.
 
For what it's worth, when you're using an Access DB through ColdFusion, you also follow the SQL convention and use %.
 
Logan said:
If you're actually in Access, you use the * but I think if you're using Jet to access the Access DB, then you have to use %. I think.
Click to expand...

Good point Logan. I think you are correct.

riley
 
well if its just really tripping cause of the quote around the wild card, which you have as a single quote ('), why don't you try doing that as an entity reference? Like &quote;

I'm not thinking about this one too much cause you probably got a fix by now...and I dont really feel like thinking right now.
 
Can somewhone tell me how can I get a name String whith LIKE. For exemple

When I search "John Bill Miller"
I can get "John Bill Sant Miller"

Thank you
 
alvezinho said:
Can somewhone tell me how can I get a name String whith LIKE. For exemple

When I search "John Bill Miller"
I can get "John Bill Sant Miller"

Thank you
Click to expand...
Easiest way might be to do something along the lines of the following:

Code: 
SELECT whatever
FROM wherever
WHERE 1=1 
 <cfloop list="#form.findname#" delimiters=" " index="thisword">
  AND Name Like ' %#thisword#% '
 </cfloop>

This turns your search string into a space delimited list and loops through it.
 
You must log in or register to reply here.
Back
Top