SQL Statement in VBA - Problem with "Like"

[Guest]

I have a small VBA programming problem that is getting frustrating. I would
like to define a record set using the "Like" operator. So far, I can not do
so, and can not figure out why. The code I have been working with is below.
The database in question has a list of 500 place names, about 50 of which
contain the work "Grand" somewhere within the phrase. The table is called
"Events"; the Field containing the word "Grand" is a text field named
"Destination."

When I define the record set as:
GCEvents = "SELECT Events.Destination" & _
" FROM Events"

Everything works fine -- a record set including all 500 records is returned
using "RecordCount" . But, when I add the line:
"WHERE ((Events.Destination) LIKE ('*Grand*'))"
I get zero records.

Most puzzling. When I use the code below as a simple SQL Query in Access,
it works fine. It returns the 50 records containing the word "Grand"
It is only when I attempt to incorporate this SQL Query in a VBA routine
that the process fails.

Any help would be appreciated.

********************************************
This is the code I am working with:
*********************************************
Dim myConnection2 As ADODB.Connection
Set myConnection2 = CurrentProject.Connection
Dim myRecordset2 As New ADODB.Recordset
myRecordset2.ActiveConnection = myConnection2

Dim GCEvents As String

GCEvents = "SELECT Events.Destination" & _
" FROM Events " & _
"WHERE ((Events.Destination) LIKE ('*Grand*'))"

myRecordset2.Open GCEvents, , adOpenStatic, adLockOptimistic

GCTotalEvents = myRecordset2.RecordCount

myRecordset2.Close
Set myRecordset2 = Nothing
Set myConnection2 = Nothing

 

[david epsom dot com dot au]

Try using ANSI wildcards: % ADO also supports ANSI wildcards under some
conditions.

(david)

 

[Richard]

Hi

Try this and see how it works

Like """ & "*" & "Grand" & "*" & """))"

Richard

 

[Guest]

No, I tried the following line:

"WHERE ((Events.Destination) Like (""" & "*" & "Grand" & "*" & """))"

It still returns a zero result.
Thanks for the attempt, however.

 

[Guest]

Jackpot!
This works. Now, why VBA works with "%" but not with "*" escapes me, but
like the tax code such things are not always susceptible to logical
analysis.

Thank you very much.

 

[Douglas J. Steele]

AFAIK ADO always requires %, not *, as the wildcard.

 

[david epsom dot com dot au]

I understand that if calling a precompiled Access qdf,
the qdf will use Jet/DOS wild cards. I'm not sure
what the rules are for switching between systems, so
I've always imagined that if you were doing sql
modifications to stored querydefs you might get
very confused...

(david)