How do I get my strSQL criteria to work in a if then else statement?

[Seth Schrock]

I'm using SQL in VBA to check if a checkbox is checked or not. I don't get any compilation errors, but when I run the code (On click event for a button), it says "Type mismatch" and doesn't error out on the code side. Here is the code:

Dim strSQL As String
 
strSQL = "SELECT EnableSpecialRate FROM tblCustomers"
strSQL = strSQL & " WHERE CustomerID = Forms!frmACHCallConfirmation!ACHCompanyID;"
 
If strSQL = true Then
     MsgBox "true"
Else
     MsgBox "false"
End If

I checked to make sure that checkbox was formated in the to be true/false instead of yes/no or on/off. If I comment out the If then else statement, it runs okay so obviously there is a problem with my criteria of strSQL = true.

Side note: the msgbox "true" and msgbox "false" will be replaced with other code. I'm just using that for testing purposes so that I know my if then else statement is working.

I'm using Access 2010.

[NeoPa]

Where to start?

Setting your string to valid SQL is one thing (and it appears you have done that successfully), but that's all you've done. SQL has to be executed in some way if it's to have any meaning as SQL, and this you haven't done. Comparing strSQL = True is functionally equivalent to saying "asdfgh" = True.

When you get around to executing the SQL in the way you want to, you then have a problem in as much as a SELECT SQL string returns a RecordSet. It makes no more sense to check the value of a RecordSet than it does a string.

Perhaps you could try to describe what you're trying to achieve in simple and clear English and maybe we could help you interpret what you're looking for.

[NeoPa]

Are you after using DLookup() by any chance? Maybe you want to find the value of tblCustomers.EnableSpecialRate for the record which matches [CustomerID] = Forms!frmACHCallConfirmation!ACHCompanyID?

[Seth Schrock]

You are correct for what I'm trying to do. Looking at what DLookup() does, it looks like the perfect thing and a bunch simpler than what I have already and from the sounds of it, I would need to add even more. Thanks so much.

[NeoPa]

DLookup() is good for a one-off. The more values you want from the record the more overhead is introduced by DLookup() and the more reason for using Recordset Processing instead. I'll leave that with you as a consideration.

[Seth Schrock]

Everything worked perfectly. I was even able to use it on my query that counted the number of records. Thanks again and I'll keep in mind what you said for any other problems I come up with.