Microsoft Jet wildcards: asterisk or percentage sign?

Solution 1:

The straight answer is that the behaviour of the wildcard characters is dependent on the ANSI Query Mode of the interface being used.

ANSI-89 Query Mode ('traditional mode') uses the * character, ANSI-92 Query Mode ('SQL Server compatibility mode') uses the % character. These modes are specific to ACE/Jet and bear only a passing resemblance to the ANSI/ISO SQL-89 and SQL-92 Standards.

The ADO interface (OLE DB) always uses ANSI-92 Query Mode.

The DAO interface always uses ANSI-89 Query Mode.

When using ODBC the query mode can be explicitly specified via the ExtendedAnsiSQL flag.

The MS Access user interface, from the 2003 version onwards, can use either query mode, so don't assume it is one or the other at any given time (e.g. do not use query-mode-specific wildcard characters in Validation Rules).

ACE/Jet SQL syntax has an ALIKE keyword, which allows the ANSI-92 Query Mode characters (% and _) regardless of the query mode of the interface, however has the slight disadvantage of the ALIKE keyword not being SQL-92 compatible (however ALIKE remains highly portable). The main disadvantage, however, is that I understand the ALIKE keyword is not officially supported (though I can't imagine it will disappear or have altered behaviour anytime soon).

Solution 2:

If you're using DAO, use asterisk (and question mark for single symbol placeholder). If you're using ADO, use percent sign (and underscore).

Solution 3:

You may find this useful:

http://msdn.microsoft.com/en-us/library/aa140104(office.10).aspx

In the query design grid and with DAO you use *, with ADO and ASP, you use %