By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,181 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,181 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,951
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "SELECT EnableSpecialRate FROM tblCustomers"
  4. strSQL = strSQL & " WHERE CustomerID = Forms!frmACHCallConfirmation!ACHCompanyID;"
  5.  
  6. If strSQL = true Then
  7.      MsgBox "true"
  8. Else
  9.      MsgBox "false"
  10. 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.
Sep 2 '11 #1

✓ answered by 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?

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,766
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.
Sep 2 '11 #2

NeoPa
Expert Mod 15k+
P: 31,766
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?
Sep 2 '11 #3

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Sep 2 '11 #4

NeoPa
Expert Mod 15k+
P: 31,766
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.
Sep 3 '11 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Sep 3 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.