How to save SQL COUNT query result using VBA in Access 2007?

Question

I am trying to count the number of records in a table that meet certain criteria. My preference is to use SQL, not Dcount, since I want to improve SQL. Here is my current code below:

Dim countString As String Dim count countString = "SELECT COUNT(*) FROM `Engagement Letters` WHERE 'Client ID' = " & Me.cboSelectClient count = CurrentDb.OpenRecordset(countString).Fields(0).Value 

Yes, I know, I used spaces in my tables and field names - I will change that. Although I think that I still have to run this request as it is, so I will leave it as it is for now.

When I run above, I get a 3464 runtime error - data type mismatch in the criteria expression. I had the dcount function work below:

 count = DCount("[Engagement Letter ID]", "Engagement Letters", "[Client ID] = " & Me.cboSelectClient) 

And also the bottom COUNT query without WHERE works fine:

 "SELECT COUNT(*) FROM `Engagement Letters`" 

My knowledge of SQL is very minimal, and my knowledge of the more advanced VBA is also very minimal, so I'm not sure where I am going wrong. Can anyone help me with this?

Answer 1

Try creating your line as follows.

 countString = "SELECT COUNT(*) FROM [Engagement Letters]" & vbCrLf & _ "WHERE [Client ID] = " & Me.cboSelectClient Debug.Print countString 

Use square brackets around the names of objects (tables and fields), which include spaces or any characters other than letters, numbers, and underscores.

You used `Engagement Letters` for the table name, and the return return lines work the same as the square brackets. They may always work equally well, but I don’t know for sure, because I use only parentheses. And brackets instead of backticks can help you avoid this error ...

 WHERE 'Client ID' = " & Me.cboSelectClient 

... that asked the db engine to compare the literal string "Client ID" with the numerical value (?) that you pulled from cboSelectClient .

I used vbCrLf between the two parts of the SELECT because I find it convenient when viewing a completed line (via Debug.Print ).