sql in vba syntax

[issactang]

my syntax as below

sqltrans = "Select * from Transactions "
sqltrans = sqltrans & "where [checked in date] IS NULL "
sqltrans = sqltrans & "And [Checked out to]= """ & rspatrons!ID & """"
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

[ADezii]

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.

[issactang]

the [checked in date] is date

[issactang]

i wrote this and worked...

sqltrans = "Select * from Transactions where isnull([checked in date]) AND [checked out to]= " & _
    rspatrons!ID

but i still want to know how to do it in separate lines :)

[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 :

sqltrans = "Select * from Transactions "
sqltrans = sqltrans & "where [checked in date] IS NULL "
sqltrans = sqltrans & "And [Checked out to]= " & rspatrons!ID
rstrans.Open sqltrans, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

PS. Please review [CODE] Tags Must be Used.

[Mihail]

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:

Debug.Print sqltrans
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 !!!!!!!

[issactang]

thanks a lot for your support and patience