Visual Basic Forum for Visual Basic Programmers VB

[Denis Marchand]

Hi,
I'm trying to issue an SQL 'SELECT' statement with the 'LIKE' operator from a VB program against a .mdb database. The code is:
Dim rs As Object, SQL
SQL = "SELECT tbl_Test.Ref, tbl_Test.Name FROM tbl_Test WHERE (((tbl_Test.Name) Like 'Mar*') AND ((tbl_Test.Deleted)=False)) ORDER BY tbl_Test.Ref;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQL, AdoContacts.ConnectionString, adOpenKeyset, adLockOptimistic '(where AdoContacts is a ADO control on my form)

This query returns an empty set while the same query issued from Access (in an Access Query) returns the correct result.
Yes, if I replace 'LIKE' with '=', then the result is OK (of course not what I want), which kinda shows that there is nothing wrong with the code and the setup (I hope). All my other SELECT statements work. Why doesn't the 'LIKE' one? Any clues?

I use VB 6.0 SP6
Many thanks in advance

Denis[/code]

[Denis Marchand]

Ooops! Found the fix by myself: the SQL 'LIKE' operator requres a '%' (percent) character as a wildcard, instead of the '*' (asterisk) character I had been using.
Funny that Access accepts the asterisk as a wildcard...

Denis

[jmcilhinney]

When using the Access GUI, it accepts the standard Windows widcards of "*" and "?" because it is a Windows application. SQL cares nothing about Windows though, and requires its own standard wildcards of "%" and "_".

[Denis Marchand]

Thanks! I figured it out, but I have to admit I wasted some time trying to find indirect ways like dynamically creating a view ( a query) in Access and then executing it but to no avail...
Problem solved now.
Thanks again

Denis