5

I'm trying to count the number of records in a table that meet a certain criteria. My preference is to use SQL, not Dcount, as I want to get better at SQL. Here's 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

Yeah I know, I've used spaces in my tables and field names - I will change that. Though I think I should still be able to run this query as is, so I will leave it as is for now.

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

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

And also the below COUNT query without the WHERE works fine:

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

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

andrewb
  • 5,200
  • 6
  • 36
  • 54

1 Answers1

1

Try building your string like this.

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

Use square brackets around object (table and field) names which include spaces or any characters other than letters, digits, and the underscore character.

For the table name, you used `Engagement Letters`, and the backticks appear to work the same as square brackets. Perhaps they always work equally well, but I don't know for sure because I use the brackets exclusively. And brackets instead of backticks might help you avoid this mistake ...

WHERE 'Client ID' = " & Me.cboSelectClient

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

I used vbCrLf between the 2 parts of the SELECT statement because I find that convenient when examining the completed string (via Debug.Print).

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 1
    Yep you got it, thanks for the help! I actually had solved it shortly after posting this, but got called into a meeting. Though I didn't quite understand why [] works and "" doesn't, so your explanation was a big help. Fixing errors is good, but gaining understanding is even better! – andrewb Jul 17 '12 at 01:15