Wild card search

[Tracy]

Please can someone tell me why the first statement works fine, but as soon as I introduce a wildecard - * -, it won't work.

Any ideas would be very much appreciated.


rs.Open "SELECT * FROM Query1 WHERE Code = 'GR1'", CurrentProject.Connection

rs.Open "SELECT * FROM Query1 WHERE Code like '*GR*'", CurrentProject.Connection

When I try this in a normal query grid it works fine, but inb code it just won't work.

Any ideas would be very gratefully appreciated.

Thanks
Tracy

 

[Mile-O]

You are not putting as semi-colon at the end of your SQL statement, try this:

rs.Open "SELECT * FROM Query1 WHERE

 Like '*GR*';", CurrentProject.Connection

 

[ColinEssex]

Hi Tracy

I think its something like this -

Like '*" & "GR" & "*'"

I've probably got the quotes wrong but its along those lines.

Col

 

[Tracy]

Nope, neither of these change anything. You don't need square brackets round the field name unless it contains a space, plus I never put the semi-colon at the end of SQL unless it's in a query grid. My first statement works fine without the semi-colon. It just seems to be the * wildcards it doesn't like.

Any other tips?

 

[Jon K]

In VBA code, the wildcard characters used in ADODB recordsets are the % and _, not the * and ? as used in DAO recordsets.