Using the Right Wildcard Characters in SQL Statements
Using the Right Wildcard Characters in SQL Statements
by Sean Kavanagh
Application: Access 97/2000
In last month's article "Use pattern-matching to quickly generate accurate query results," we looked at how to use wildcard characters in the query design grid to simplify query creation and accommodate for data inconsistencies. Unfortunately, working with wildcards gets a little more complicated when you're working directly with SQL statements. Even if you've mastered using wildcards in queries, you may be in for a shock if you switch to using ADO in your applications. You're likely to find that SQL statements that worked just fine in Access 97 code suddenly cause problems when run in Access 2000 with ADO.
In this article, we'll examine the differences between wildcards in DAO and ADO so you know what to watch out for when your code incorporates SQL statements. We'll start by using DAO wildcards in the query design grid and incorporating the related SQL statement in code. Then we'll show you the adjustments you need to make to run the code under ADO. We'll also reveal an undocumented shortcut you can use to make the query design grid accept ADO's wildcards so that you can ensure your query's working as intended before you begin working with the underlying SQL statement.
The ADO monkey wrench
Though Access supports SQL statements, Access SQL doesn't completely adhere to ANSI SQL standards. In Access 97, Access SQL most closely resembles the SQL-89 standard. However, with the release of Jet 4.0 in Access 2000, Access SQL was updated to more closely emulate the SQL-92 standard through syntax exposed by ADO and the Microsoft Jet OLE DB Provider. This impacts many aspects of using SQL statements in code, one of which is wildcard behavior. The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character. There's no single-digit wildcard available through ADO that equates to DAO's pound sign wildcard (#); however, character lists and ranges behave the same.
The big problem with this is that it's easy to be completely unaware of the change. The query design grid still supports the DAO wildcards in Access 2000, as we saw in last month's article. Since it's a common practice for developers to create queries in the design grid, then switch to SQL view to copy and paste the created SQL statement into code, this can create surprise when a simple SELECT query that worked moments before suddenly raises an error.
Creating SQL statements with the query design grid
To best show the impact that ADO has on SQL statement wildcard characters, we'll create a simple query in the design grid and then look at what Access displays in SQL view. To illustrate our example, we'll use the table and data shown in Figure A. Once you've set up the sample data, close the table and switch to the Queries sheet of the Database window.
Figure A: We'll use this sample data to show the problems that arise when creating SQL
statements with the query design grid.
At this point, click the New button to begin creating a new query. When the New Query dialog box appears, double-click on Design View to display the query design grid. Select tblUsers in the Show Table dialog box and click Add, then close the dialog box. Next, drag the FirstName and LastName fields to the design grid.
The final step is to add our criteria. We'll use wildcards to retrieve the various spellings of the name Kreskin. To do so, click in the Criteria text box in the LastName column and enter
This is a fairly standard query, like any other you might create with the design grid. Now, let's take a look at the SQL statement behind this query that Access has created. To do so, choose SQL View from the View dropdown menu. Access displays a statement resembling
SELECT tblUsers.FirstName, tblUsers.LastName FROM tblUsers WHERE (((tblUsers.LastName) Like "?r?s??n*"));
Working with DAO
In Access 97, the statement Access created can be copied and modified to create a SQL string that runs just fine in a DAO procedure. For example, we incorporated a cleaned up version of the statement in the example procedure shown in Listing A. The WildcardsInDAO procedure, when run in a DAO environment, lists the names that match the specified Like pattern in the Immediate/Debug window. If you want to try the procedure yourself, enter the code in a new module, press [Ctrl]G to display the Debug window, and click the Go/Continue button.
Listing A: DAO wildcard example
Sub WildcardsInDAO() Dim rst As DAO.Recordset Dim strsql As String strsql = "SELECT FirstName, LastName " & _ "FROM tblUsers " & _ "WHERE LastName Like ""?r?s??n*"";" Set rst = CurrentDb.OpenRecordset(strsql) Do Until rst.EOF Debug.Print rst.Fields("LastName") rst.MoveNext Loop End Sub
Although this particular SQL statement is simple, you can see how creating a query first in the design grid can save time when figuring out the syntax for more complex queries. To get our query into shape we simply eliminated the extraneous parentheses Access inserts along with the field qualifiers that weren't necessary in this case.
One aspect of the conversion that can cause difficulty is getting the quotations around your Like criteria correct. In order to incorporate a double quotation mark character (") within a string, as is required to specify your comparison pattern, you must use two consecutive double quotation marks. Otherwise, the quotation mark is interpreted as the end of the string, which will cause your code to fail.
Working with ADO
Trying to use the same statement in an ADO procedure, such as the one shown in Listing B, doesn't return anything to the Immediate window because it looks for literal matches to the question mark and asterisk characters. In order to return records, the WHERE clause must be changed to:
"WHERE LastName Like ""_r_s__n%"";"
Listing B: An ADO attempt at using wildcards
Sub WildcardsInADO() Dim rst As New ADODB.Recordset Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection Dim strsql As String strsql = "SELECT FirstName, LastName " & _"FROM tblUsers " & _"WHERE LastName Like ""?r?s??n*"";" rst.ActiveConnection = cnn rst.Open strsql, cnn Do Until rst.EOFDebug.Print rst.Fields("LastName")rst.MoveNext Loop End Sub
Using ADO wildcards in the query design grid
Making the necessary adjustments to change DAO wildcards to ADO wildcards may be a simple task, but if you're using ADO wildcards in your code you may prefer to also do so in the query design grid. Although it's undocumented, there is a way to use ADO wildcards in Access 2000's query design grid. Instead of using the Like operator, use ALike. For instance, you could use the criteria
to replace our previous Kreskin example. The ALike operator works in SQL statements used in code as well, allowing you to easily take advantage of using the query design grid to create SQL statements.
Go wild with query criteria
Although you'll often run saved queries, you just as often have to execute SQL statements through code. The query design grid can help simplify the task of generating SQL strings for your code thanks to the SQL View. As long as you're aware of the inconsistencies between wildcards in DAO and ADO you can use pattern-matching characters in your SQL statement criteria to perform flexible searches.
Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.