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

sql in vba syntax

P: 25
my syntax as below
Expand|Select|Wrap|Line Numbers
  1. sqltrans = "Select * from Transactions "
  2. sqltrans = sqltrans & "where [checked in date] IS NULL "
  3. sqltrans = sqltrans & "And [Checked out to]= """ & rspatrons!ID & """"
  4. rstrans.Open sqltrans, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

error msg is datatype mismatch in criteria expression.

I tried the sql directly, I could succeed, so i assumed
it is something to do with the syntax.
how to testify null value by integrating to vba ?
how to solve the aforementioned problem ?
thank
Dec 30 '11 #1

✓ answered by NeoPa

You have just confirmed what ADezii suspected was your problem. To keep your code similar to how it was before, yet fix this little problem, try :
Expand|Select|Wrap|Line Numbers
  1. sqltrans = "Select * from Transactions "
  2. sqltrans = sqltrans & "where [checked in date] IS NULL "
  3. sqltrans = sqltrans & "And [Checked out to]= " & rspatrons!ID
  4. rstrans.Open sqltrans, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
PS. Please review [code] Tags Must be Used.

Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
sqltrans = sqltrans & "And [Checked out to]= """ & rspatrons!ID & """"
Is ID a Numeric or String Data Type, since if it is Numeric, then the Syntax is incorrect.
Dec 30 '11 #2

P: 25
the [checked in date] is date
Dec 30 '11 #3

P: 25
i wrote this and worked...

Expand|Select|Wrap|Line Numbers
  1. sqltrans = "Select * from Transactions where isnull([checked in date]) AND [checked out to]= " & _
  2.     rspatrons!ID
but i still want to know how to do it in separate lines :)
Dec 30 '11 #4

NeoPa
Expert Mod 15k+
P: 31,186
You have just confirmed what ADezii suspected was your problem. To keep your code similar to how it was before, yet fix this little problem, try :
Expand|Select|Wrap|Line Numbers
  1. sqltrans = "Select * from Transactions "
  2. sqltrans = sqltrans & "where [checked in date] IS NULL "
  3. sqltrans = sqltrans & "And [Checked out to]= " & rspatrons!ID
  4. rstrans.Open sqltrans, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
PS. Please review [code] Tags Must be Used.
Dec 30 '11 #5

100+
P: 759
You say that your SQL work.
So I suspect that is anything wrong when you construct the string sqltrans. Debug that as follow:
After line 3 insert this code:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. Debug.Print sqltrans
  5. Stop

Run your program then, when the code stop, press CTRL+G. This will open the Immediate window where you can see what value has sqltrans variable.
Now you have two options:
First is to look at this string and see if somewhere something is wrong;
The second option is to create a new query, switch to SQL view, copy-paste the string in the SQL view and switch to Design view to see what happen.

Happy New Year to all !!!!!!!
Dec 31 '11 #6

P: 25
thanks a lot for your support and patience
Jan 5 '12 #7

Post your reply

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