The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS Access like operator

I'm confused.

I need to query some descriptions by the like operator.
MS Access 2003 help has the following example
matches with
aa, aBa, aBBBa
does not match with

It uses the asterisk wildcard.
Wrong, I can make the query work only if I use the % wildcard.

Am I missing something or is it a MACRO mistake?
Sevenoaks Send private email
Friday, August 04, 2006
From Office Online:

"Microsoft Jet SQL supports both ANSI SQL wildcard characters and Microsoft Jet-specific wildcard characters to use with the Like operator. The use of the ANSI and Microsoft Jet wildcard characters is mutually exclusive. You must use one set or the other and cannot mix them. The ANSI SQL wildcards are only available when using Jet 4.X and the Microsoft OLE DB Provider for Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft OLE DB Provider for Jet and Jet 4.X."

Also see:
l belk
Friday, August 04, 2006
I pretty sure MS went from * to % as the wild card character in the transition from DAO to ADO.

DAO (being specifically designed with Access in mind) uses *.
ADO (intended for more universal data access) uses %.
Marcus from Melbourne
Saturday, August 05, 2006
This article may be of some assistance:

Refer to the section called 'Wild Card Characters'.
Marcus from Melbourne
Saturday, August 05, 2006
Marcus is dead on ….

From the debug window, I can type in the following
DAO example

? currentdb.OpenRecordset("select count(*) from altry where myname like 'a*'")(0)

The above gives a result of 2…

Now, lets try this in ADO….
? currentproject.Connection.Execute("select count(*) from altry where myname like 'a*'")(0)

It gives zero.

And, when I use % for both of the above..the results are reverse.

In the query builder, you have to use *.

However, you can go into tools->options->tables/Queries
And click on the sql ansi option.

When you do this, then the query builder will work with a %.
However, your DAO code will STILL require a *

So, if you are using the query builder…and % works, then the sql ansi 92 has been turned on…

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Sunday, August 06, 2006
Thanks Albert and all the guys, that was what I was missing...
Sevenoaks Send private email
Monday, August 07, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz