Use of Like * Works in MS-Access but Not VBA

Question

I have a simple query but am running into problems using LIKE in VBA. My SQL string in VBA is:

stsql1 = "Select Top 25 data.* from data where data.Description Like ('*') "

When I run this sql string in my VBA code I get no records returned, but if I copy/paste the same string into a query in SQL View in MS Access, the query returns the values I expect. Is there a trick to using the "Like" syntax in VBA?

I can provide additional code and a small version of the database if that would help.

Answer 1

For SQL, the database engine will accept either single or double quotes as text delimiters. So either of these 2 WHERE clauses will work.

WHERE some_field Like '*'
WHERE some_field Like "*"

VBA however only accepts double quotes as text delimiters, so you would have to use the second form.

Two other points about your SELECT statement:

Select Top 25 data.* from data where data.Description Like ('*')

1. TOP [number] is arbitrary without an ORDER BY clause
2. You don't need parentheses surrounding your Like pattern ... you can use Like "*"

If your VBA code is using ADO with that SELECT statement, you must change the wild card character from * to % ...

WHERE data.Description Like '%'

Answer 2

In ADO/VBA, you have to use % instead of * as the wildcard. I ran into this a couple times in the past ....