Wildcard * in VBA search using WHERE

[Joe Valentine]

I wanted to open a recordset in VBA using a query (SQL) with a WHERE clause, where the field contains any part of the letters "venl"

The query would not work when I used *venl* but it worked fine when I used %venl%

This did NOT work:
Select * from [table] WHERE [field] Like "*venl*"

This works perfectly:
Select * from [table] WHERE [field] Like "%venl%"

Any explanations for the wildcard %? I used it because I saw it in some of my earlier code but don't remember where I got it from.

[Rabbit]

Are you using a ADODB connection to connect to another DBMS? Such as SQL Server? I'm not sure what you mean by explanation. Different DBMS use different wild cards. SQL Server uses % while Access uses *.

[Joe Valentine]

I'm using only Access. here's the gist of the module I use to open the recordset. With that recordset open, I find I must use % rather than *.

If Len(strCnn) = 0 Then
        If cnn Is Nothing Then
            Set cnn = CurrentProject.Connection
        End If
    Else
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = strCnn
        cnn.Open
    End If
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = lngCursorLoc
        .CursorType = lngCursorTyp
        .LockType = lngLockType
        .ActiveConnection = cnn
        .Source = strSource
        If Nz(fUseTable, 0) = 0 Then
            .Open
        Else
            .Open Options:=fUseTable
        End If
    End With
    Set OpenADORecordset = rst
    Set rst = Nothing

[Rabbit]

From Microsoft:

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.

[NeoPa]

Since Access 2000 there has been an Access Option to enable ANSI-92 standard compatibility. Thus, some Access databases use the ANSI-92 standard, while others still use the earlier ANSI-89 standard. This has already been mentioned in previous posts, but for more information see ANSI Standards in String Comparisons.

ADODB, as a library, may well insist on the ANSI-92 standard in all its dealings. This can make life complicated within a project based on a database whose ANSI-92 setting is not turned on (Thus still using the ANSI-89 standard). SQL created within the query grid by Access itself, is then sometimes incompatible with the SQL required when using ADODB. You may want to consider using the DAO library instead, for work which is exclusively Access related (It's quite limited when dealing generally with databases, but within Access exclusively, it is closer to normal use than ADODB is. Clearly that means it is less portable generally, but within the Access environment it is a closer match).