SQL Like Operator with CF & Access

General Web Coding Cold Fusion MX
1

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

		<cfset addEventArtistCrit = "AND ((t1.eventArtists) Like '" &  #dbWildCard# & toString(#url.artistId#) & #dbWildCard# & "')">

<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. Like 11, but if I have the quotes and the wildcards I get an error, eg. Like "*11*"

Here is the error:

Data type mismatch in criteria expression

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

Any ideas?

Thanks,

Brent

2

I’ll admit that I haven’t really read your post closely, but what happens if you try:

LIKE ‘%11%’

3

[QUOTE=peskypup]
I’ll admit that I haven’t really read your post closely, but what happens if you try:

LIKE ‘%11%’
[/QUOTE]

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.

4

[QUOTE=hatton]
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.
[/QUOTE]

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

5

[QUOTE=riley]
Access does not support % as a wildcard character. It does, in fact, support the * character, as well as a few others.
[/QUOTE]

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.

6

For what it’s worth, when you’re using an Access DB through ColdFusion, you also follow the SQL convention and use %.

7

[QUOTE=Logan]
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.
[/QUOTE]

Good point Logan. I think you are correct.

riley

8

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.

9

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

10

[QUOTE=alvezinho]
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
[/QUOTE]

Easiest way might be to do something along the lines of the following:

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.

11

LIKE “%WHATEVER” … pls use double quote, it’ll work