Getting a Type mismatch error on ampersand (&) in recordset SQL statement

[VicM]

I'm using Access 2010 on a Win7 Pro box.



I have a search routine (event) on a form which resets the RecordSource based on the search string to show all records containing that string. This construct works well. However when the string isn't found, the form shows a blank record such as a one gets for a new input since the recordset is empty.

So I decided I want to capture the recordset if it's empty, and display a message to that effect instead of showing a blank record. I copied the SQL string and set a dimensioned recordset (recSet) equal to it. This code COMPILES, but when I try executing the event, I get a Type mismatch error which points to the entire Set recSet statement . (Me.txtTtl.Value is the string being searched for.) The following is a code snippet of the event:

           Dim strFind as String
           Dim recSet as Recordset
           Dim db as Database

        strFind = "[Title] Like '*" & Me.txtTtl.Value & "*'"
        
       Set db = CurrentDB

       Set recSet = db.OpenRecordset("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;", dbOpenDynaset, dbSeeChanges) 
      
        If recSet.EOF = True And recSet.BOF = True Then
            MsgBox "No records found.", vbOKOnly, "No Records"
            btnRefresh_Click
            Exit Sub
        End If
        
        Me.RecordSource = "SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"

Note that if I remove all the Green code, the event compiles without error and works. The SQL statement for the Me.RecordSource is IDENTICAL to the SQL statement for the recordset (recSet) since I just copied it from the Me.RecordSource statement.

I then decided to approach this differently. I changed the code according to the following snippet and got a Type mismatch for the ampersand (&) in the SQL statement for the recSet recordset:

                Dim strFind AS String
                Dim recSet AS Recordset

        strFind = "[Title] Like '*" & Me.txtTtl.Value & "*'"

        Set recSet = ("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"

        If recSet.EOF = True And recSet.BOF = True Then
            MsgBox "No records found.", vbOKOnly, "No Records"
            btnRefresh_Click
            Exit Sub
        End If

        Me.RecordSource = "SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"
        DoCmd.Requery

This has got me head scratching.

Vic

PS I Decompiled and tried recompiling getting the same error for both versions.

[Micron]

Just a quick guess but I suspect txtTitle is Null. In these situations, you always output the sql construct to the immediate window and review it. If nothing is obviously wrong, copy the output to a new query in sql view and try switching to datasheet view. I do that out of habit because sometimes the sql is for an action query and datasheet view won't actually run and alter anything. The point is, if in sql view and you attempt datasheet view and the query balks, the offending part (or close to it) usually gets highlighted.

As for the initial goal, applying a new recordsource may result in no records as you say. Therefore, a count of a recordset is one way (moving last isn't really necessary as you probably just care that there is at least one). Another way is to test the recordset clone of the newly applied recordsource. Perhaps another would be to test the form's recordset.recordcount. Either way, you might want to store the original (or last) source property and revert back to it, or just let the form go blank.

[moke123]

Sometimes you get weird errors if you dont explicitly declare your Db and Recordset - Dim Db as DAO.Database, Rs as DAO.Recordset

[VicM]

Micron,

I actually trap a null txtTitle field. So it's never null. And I do revert the RecordSource to the original.

Thanks

[VicM]

moke123

Hot dang! That did the trick.

Thanks,
Vic

[ssanfu]

In addition to what Micron & moke123 said, there is an error (actually 2) in your 2nd snippet of code.

I then decided to approach this differently. I changed the code according to the following snippet and got a Type mismatch for the ampersand (&) in the SQL statement for the recSet recordset:

                Dim strFind AS String
                Dim recSet AS Recordset

        strFind = "[Title] Like '*" & Me.txtTtl.Value & "*'"

        Set recSet = ("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN  Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON  Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"

The first error in the code, as it sits now, is that there is an opening parenthesis after "Set recSet =" and no closing parenthesis.
This is also part of the 2nd error.
You defined "recSet" as a recordset, so you CANNOT just set a string equal to the record set object. You MUST open the record set OBJECT with the SQL as the source of the records.

So if you change the command line to

        Set recSet = Current.Openrecordset("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
        "FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN   Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON   Authors_1.AuthorIDX = Books_Read.Author2 " & _
        " WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;", dbOpenDynaset, dbSeeChanges)

you will not get a Type mismatch for the ampersand (&) in the SQL statement for the recSet recordset

[VicM]

ssanfu,

Thanks for your eagle eye!

However, I used the first example in the event and declaring DAO.Database and DAO.Recordset did the trick. So I'm going to stay with that.

But it's good to know.