ACC2002: FindFirst that uses LIKE behaves differently in DAO snapshots and DAO dynasets (282383)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282383
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you perform a search that uses the FindFirst method and LIKE without a wildcard character ("*"), you get different results in a DAO snapshot recordset and a DAO dynaset recordset. Using a DAO snapshot, the search acts as if the wildcard character ("*") was added to the end of the search string. For example, in a table that contains the names Greg, Jacob, Nick, John, Joe, and Michael, searching for "LIKE Jaco" finds a match in a DAO snapshot. However, FindFirst does not find a match in a DAO dynaset.

RESOLUTION

If you want the search to act as if it appends the wildcard character ("*") to the end of your search string, use a DAO snapshot.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

  1. Create a new Access database.
  2. Create a table named Names that contains a Text field named FirstName.
  3. Add the names Greg, Jacob, Nick, John, Joe, and Michael to the Names table.
  4. Enter the following code in a new module in this database:
    Public Sub TestLIKE()

    Dim dbs As DAO.Database
    Dim rsNamesSnapshot As DAO.Recordset
    Dim rsNamesDynaset As DAO.Recordset
    Dim strSearch As String
    
    Set dbs = CurrentDb
    Set rsNamesSnapshot = dbs.OpenRecordset("Names", dbOpenSnapshot)
    Set rsNamesDynaset = dbs.OpenRecordset("Names", dbOpenDynaset)
    
    strSearch = "Jaco"
    
    ' Search the Snapshot recordset.
    rsNamesSnapshot.FindFirst "FirstName LIKE '" & strSearch & "'"
    If rsNamesSnapshot.NoMatch Then
        Debug.Print "In rsNamesSnapshot, " & strSearch _ 
         & " was not found"
    Else
        Debug.Print "In rsNamesSnapshot, looked for " & _
         strSearch & " and found " & rsNamesSnapshot("FirstName")
    End If
    
    ' Search the Dynaset recordset.
    rsNamesDynaset.FindFirst "FirstName LIKE '" & strSearch & "'"
    If rsNamesDynaset.NoMatch Then
        Debug.Print "In rsNamesDynaset, " & strSearch & " was not found"
    Else
        Debug.Print "In rsNamesDynaset, looked for " & strSearch & _ 
         " and found " & rsNamesDynaset("FirstName")
    End If
    
    ' Print a blank line.
    Debug.Print
End Sub
  5. On the Tools menu, click References, and then add a reference to the Microsoft DAO 3.6 Object Library.
  6. In the Immediate window, type TestLIKE, and then press ENTER. Note that the match was found with the snapshot recordset, but not the dynaset recordset.
Modification Type:MinorLast Reviewed:2/1/2006
Keywords:kbbug kbnofix KB282383
©2004 Microsoft Corporation. All rights reserved.