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.

Sigman

Should not be any difference. Please post your surrounding VBA code. – Bisector

Show us enough of the code context so we can understand how you're attempting to use the SQL statement in VBA, and whether you're dealing with DAO or ADO here. – Psychopathy

See this related question. – Stigmasterol

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 '%'

Psychopathy

Answer 2

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

Addiction

It's not VBA, but ADO that causes you to have to use a different set of wildcards than you would in the Access query builder. Everybody posting here should read @onedaywhen's article here: #719615 – Level

Answer 3

Realize that there are at least 2 (yes two!) LIKE operators here.

One is the LIKE operator of VBA.

The other is the LIKE operator of the SQL of the database you are attached to.

The usual wildcards in SQL are % (for any # of any characters) and _ (for one of any character).

Know also that MS Access can open databases that aren't Access; it could be Microsoft SQL Server, or Oracle or IBM DB2. (BTW, the database that is normal for Access is called Microsoft JET.) You may be sheltered from that truth when you create a Query object in Access - in that circumstance, you are using JET SQL even when it's a linked table you are querying.

However, under VBA, when using either DAO or ADO, you're talking directly to whatever the database system happens to be, in which case you MUST use the SQL of that specific system.

OK, short answer: Use % like cularis said.

Threephase

The Access default wildcards are * and ?, not % and _ -- you have to be using ANSI 92 mode within Access to use the %/_ wildcards, or be accessing the Jet/ACE data with an interface that uses ANSI 92 mode by default (e.g., OLEDB). So, basically, THIS ANSWER IS WRONG. – Level

There's another wrong thing about this answer: When using DAO or ADO, you may not be talking directly to whatever the database system happens to be, because you can open a Jet database in DAO and then query a linked table (linked to, for example, SQL Server). In this case, you will have to use Jet syntax unless you create a pass-through query. – Wherewithal

@phoog: You're right about the linked table, of course. But it's now a case of splitting hairs; the underlying DB system is JET utilizing its linked table feature (even if its SQL Server that is the true system that stores the data). – Threephase

@David-W-Fenton: The above answer is correct. The OP had an issue with the LIKE operator; and the OP has indicated that cularis' answer to use "%" solved the problem. And the usual wildcards in SQL are % and _. What JET SQL uses for wildcards by default is not the usual. – Threephase

The question is tagged MS-ACCESS, which means that what is "usual" is what is the case in MS-ACCESS, not in other databases. Thus, it seems quite obvious to me that the "usual" wildcards in the context of this question and its tags would be the default Access ones, * and ?. There is no mention in the question of any database engine other than the implied Jet/ACE (because of the MS-ACCESS tag), and nothing in the question that would indicate a context where anything else should be used (such as using a passthrough query to SQL Server). – Level

Indeed, the only way that %/_ could be the correct wildcards is if the original questioner is using ADO instead of the default standard DAO for interacting in code with the database. There is no indication of that in the question, though the fact that %/_ works is a pretty strong indicator that it is what is going on. It's pretty clearly a badly-worded question, seems to me. – Level

Answer 4

I can't add a comment, but I think it would be worth noting that you have to use % even if you are querying MS Access.

(example: Outlook VBA runs query on an Access database. The proper query is select * where user like '%bob%', even though this query would not work if plugged directly into an MS Access query).

Engracia