Too Few Parameters

[Design by Sue]

I am using the following code to determine if there are no records that match the criteria and if so display a message box. I have tested the code by pasting the SQL in a query SQL window and running it and it works perfectly. Yet I am getting the error message Too Few Parameters - Expected 1. Can someone see my error? The error message trips on the line "Set rs = db.OpenRecordset(strSQL)"

Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb

strSQL = "SELECT MainTBL.QTDate, Year([QTDate]) & Month([QTDate]) AS [Match], SubTBL.Observation FROM MainTBL INNER JOIN SubTBL ON MainTBL.ID = SubTBL.LinkID WHERE (((Year([QTDate]) & Month([QTDate]))=[Forms]![Switchboard]![Match]) AND ((SubTBL.Observation) Like 'Material Defect'))"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)

If rs.Fields.Count = 0 Then


    MsgBox "There are no records in this report.", vbExclamation, "No Records"
    Set rs = Nothing

    Exit Sub
End If

Thanks
Sue

 

[pbaldy]

You have to concatenate the form value into the string.

 

[Design by Sue]

pbaldy - sorry that is a bit over my head - can you help me on it? Please

 

[pbaldy]

Along the lines of:

...='" & [Forms]![Switchboard]![Match] & "') AND...

I included the ' as a delimiter since I believe your value will be text. Remove them if you get a type mismatch error.

 

[Design by Sue]

Thank you - that was correct.

For others that may be trying this I had an error in my code.

Instead of If rs.Fields.Count = 0 Then

The correct line is If rs.RecordCount = 0 Then to test for the record count not the field count.

Thanks as always pbaldy!

Sue

 

[pbaldy]

Happy to help!

 

[namliam]

Strickly speaking though you are concatinating the year and month, you should still be left with a number... if you use a number it should (theoretically) be faster
Year([QTDate]) * 100 + Month([QTDate])
NOTE: this results in 201409 instead of 20149, which obviously is better as well both in numbers and in strings... because you get (ordered) strings
20141
201410
201411
201412
20142
...
20149
Or in numbers
20131
20139
20141
20149
201310
201311
etc...
Adding the zero prevents that and also makes it more readable (IMHO), makes it ISO as well

You are using a LIKE but not using any wildcards in your query, which makes the LIKE act like a =. with the 'problem' again (theoreticaly) = should be faster than LIKE. Worse, if you have a successor 10 years from now looking at your database.... It looks sloppy

Since you are intrested in a recordcount, you only get the proper result if you check AFTER you do a rs.Movelast, which in and upon itself can cause problems. If you want to count the number of records returned why not do it in the query directly?

Finaly readable code is maintable code! That isnt only applicable to VBA, but also to SQL

All that put together...

...
strSQL = "SELECT Count(*) as NumberOfRecords " & _
        " FROM       MainTBL " & _
        " INNER JOIN SubTBL   ON MainTBL.ID = SubTBL.LinkID " & _
        " WHERE Year([QTDate]) * 100 + Month([QTDate])="& [Forms]![Switchboard]![Match] "" & _
          " AND SubTBL.Observation = 'Material Defect' "
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)

If rs!NumberOfRecords = 0 Then

Sorry to be a pain, but I hope this helps you in a small way... Even if Paul already took care of the actual problem