Anyone who deals with SQL and Access queries is probably familiar with the LIKE statement and the use of the standard wildcards (% or *).

Less documented however is the additional wild cards that you should be aware of if you are building queries that will be run against your database when constructing more complicated LIKE statements.

The following describes the wildcard’s that are available in Microsoft SQL.

In addition after an MS SQL LIKE statement you can include an additional definition of an escape character, which can be placed in front of a wildcard character so that it is treated as a regular character or you could the square brackets as escape characters for your query, for example: –

SELECT * FROM [discounts] WHERE [discount] LIKE ’30[%]’ 
                    or
SELECT * FROM [discounts] WHERE [discount]
    LIKE ’30!%’ ESCAPE ‘!’

This will select any discounts that are 30%.

Reference: – http://msdn2.microsoft.com/en-us/library/ms179859.aspx

In Microsoft Access the wildcards and syntax is slightly different but similar

The following describes the wildcard’s that are available in Microsoft Access.

In Microsoft Access there is no ESCAPE definition so you must use square brackets, for example: –

SELECT

* FROM [discounts] WHERE [discount] LIKE ’30[%]’

This will select any discounts that are 30%.

Query Syntax in Microsoft Access on Linked SQL tables 

Now as anyone who has worked with Microsoft Access linked SQL tables will likely know, performing like queries can be complicated… this is because it can sometimes be difficult to determine where a SQL statement/query might be executed (on the SQL Server or my the local Microsoft Access engine).

As a general rule of thumb, if you are running the statement (Microsoft Access query or Access DAO Recordset) directly against the linked table, it will use the SQL Server syntax. For example: –

SELECT

* FROM tblContacts
    WHERE Firstname LIKE ‘Pa%’ –[WILL WORK]

If however your WHERE clause is in a join query or your query runs some kind of function on some of the data. For example : –

[a bad example at that, yes I would never recommend a statement like this]
SELECT Nz(contactid, –1), * FROM tblContacts
    WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

This statement would not return the correct results and would have to be modified to the following: –

SELECT

Nz(contactid, –1), * FROM tblContacts
    WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

A more likely scenario would be where you are joining two tables within your Microsoft Access query and you needed to perform a like statement against it. For example: –

SELECT

* FROM tblContacts
    LEFT JOIN tblCompanies ON
    tblContacts.CompanyID = tblCompanies.CompanyID
    WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

In MS Access this statement would not return the correct results, as the join is performed by MS Access and would have to be modified to the following: –

SELECT

* FROM tblContacts
    LEFT JOIN tblCompanies ON
        tblContacts.CompanyID = tblCompanies.CompanyID
    WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

This also applies to any MS Access Queries based other queries, even if the other Query is a direct Query against a linked SQL table.

Something to remember when generating queries from code

Now you are most likely writing code that will generate your LIKE statements based on a user’s selections. This applies whether you are coding from MS Access or through ASP, ASP.NET, VB, etc… but not if you are running through parameterised objects, for example a .NET 2.0 dataset query.

If you have to code in your SQL you need to remember to change key characters, this means these reserved wildcards as well as the standard single quotes. For example (in VBA): –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace(someVariable, "’","”",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges) ‘[WILL NOT WORK]

Though this will work in most situations, if "someVariable" contained any of the wildcard characters, you will experience unexpected results. So you should really do something more like: –

SET

myRS = CurrentDB.OpenRecordset( _
   "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace( _
     Replace( _
      Replace( _
       Replace( Replace( _
        Replace(someVariable, "’","”",1,-1,1) _
       , "%","[%]",1,-1,1), "#","[#]",1,-1,1) _
      , "[","[[]",1,-1,1) _
     , "]","[]]",1,-1,1) _
    , "_","[_]",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges)
‘[WILL WORK IF RAN AGAINST SQL LINKED TABLE]

Or if ran against MS Access table or situation described above: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
     Replace( _
      Replace( _
       Replace( _
        Replace( Replace( _
         Replace(someVariable, "’","”",1,-1,1) _
        , "*","[*]",1,-1,1), "#","[#]",1,-1,1) _
       , "[","[[]",1,-1,1) _
      , "]","[]]",1,-1,1) _
     , "?","[?]",1,-1,1) & "*’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY]

As with any form of coding it is usually a good idea to extract out common functions into a small library that can be used throughout your applications.

As such if you do build queries based on user input I’d recommend using a standard function to tidy up any users data and prevent possible conflicts or security flaws. The function could look something like (VBA): –

Function

fnTidyLikeQuery(ByVal myString As Object) As String

        On Error Goto ExitHere
    ‘as it does no harm to escape all characters 
    ‘(SQL or Access specific) this function can 
    ‘take care if both).

    If isNull(myString) Then
        Goto ExitHere
    Else
        fnTidyLikeQuery = _
            Replace( Replace( _
             Replace( Replace( _
               Replace( Replace( _
                Replace( Replace( _
                 myString _
                , "’","”",1,-1,1), "#","[#]",1,-1,1) _
               , "*","[*]",1,-1,1), "[","[[]",1,-1,1) _
              , "%","[%]",1,-1,1), "_","[_]",1,-1,1) _
             , "]","[]]",1,-1,1), "?","[?]",1,-1,1) 
    End If

    Exit Funtion
ExitHere:
    fnTidyLikeQuery = ""
End Function

Function

fnTidyQueryString(byVal myString as Variant) as String

    On Error Goto ExitHere

    If isNull(myString) Then
        Goto ExitHere
    Else
        fnTidyQueryString= Replace(myString, "’","”",1,-1,1) 
    End If

    Exit Funtion
ExitHere:
    fnTidyQueryString = ""
End Function

A sample query based on the above functions would look something like: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘*" & _
    fnTidyLikeQuery(someVariable) & "*’ AND Surname = ‘" & _
    fnTidyQueryString(someOtherVariable) & "’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY (uses * as wildcard)]

Some more samples of LIKE statements

A few weeks back I had a problem on a clients site, our application which interacts with Word files, was falling over when trying to create a Word object. This was not a problem with our coding as it has been working fine for many years and this problem was specific to a couple of workstations.

The error given was "-2147319779 Automation error Library not registered".

This error does happen if parts of Office become corrupt and sometimes if the normal.dot template file is corrupted, so tried all the usual fixes to correct these problems. None of these seemed to work and correct the fact that it could not create a Word object.

On further investigation I had found that the client had decided to try out Office 2007 on the two workstations in questions, they later decided they did not like it that much, uninstalled and re-installed Office 2003.

When Office 2007 is uninstalled, by default it leaves behind it’s Interop Assemblies. These must be removed separately, which the users had not done. Uninstalling ”Microsoft Office 2007 Primary Interop Assemblies” though the control panel -> add and remove programs, corrected the problem.

Problem: error code "-2147319779 Automation error Library not registered" when trying to work with Word files or Excel from separate application.
Cause: Installing Office 2007 and then removing it at later date replacing with previous version.
Resolution: Uninstall ”Microsoft Office 2007 Primary Interop Assemblies”.
Cause of Problem: Microsoft Office. 

Paul B
MCP

Had an issue today, where the clients system would always prompt the users to open MS Access 2003 Runtime in safe mode, even having cancelled the error and closing it which should reset it.

Error message on startup was "Access failed to start correctly last time. Starting Access in safe mode will help you correct or isolate a startup problem in order to successfully start the Program. Some functionality may be disabled in this Mode. Do you want to start Access in this Mode?".

Sometimes if file was ran directly it would open OK, but if file was ran from another app or the run command like ‘msaccess.exe "c:\mydb.mde" /excl’ (for example to ensure the file was locked as exclusive to the user opening it, or to pass any command line parameters to the Access file), you would always be prompted with the message.

Spent several hours trying to find a solution to this, uninstalling files and reinstalling, putting on the full version of Office 2003 Pro, manually deleting all registry keys and reinstalling, ‘Detect and Repair’ menu option, etc. Nothing would prevent it from happening.

Having been unable to find any direct references to this situation I found some message board comments that pointed to a sub-setting in the registry key

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Access\Resiliency

For a similar issue with Access 2002, recommending deleting the registry key. The entry the message board mentioned was not there for the equivilant Access 2003 key and deleting the entire key had no effect.

After spending a significant amount of time searching for any other references, found a vague reference to something similar happening with Outlook 2000 and a similar key for Outlook 2000 buried in the Terminal Services key tree. Managed to trace the Access 2003 version of this key value down to

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Terminal Server\Install\Software\Microsoft\Office\11.0\Access\Resiliency

Deleting this key resolved the problem and Access files would open correctly again!

Paul B
MCP