473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

sql in vba syntax

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.

6 2021
ADezii
8,834 Expert 8TB
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
the [checked in date] is date
Dec 30 '11 #3
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
32,556 Expert Mod 16PB
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
Mihail
759 512MB
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
thanks a lot for your support and patience
Jan 5 '12 #7

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

Similar topics

699
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro...
22
by: Tuang | last post by:
I'm checking out Python as a candidate for replacing Perl as my "Swiss Army knife" tool. The longer I can remember the syntax for performing a task, the more likely I am to use it on the spot if...
14
by: Sandy Norton | last post by:
If we are going to be stuck with @decorators for 2.4, then how about using blocks and indentation to elminate repetition and increase readability: Example 1 --------- class Klass: def...
16
by: George Sakkis | last post by:
I'm sure there must have been a past thread about this topic but I don't know how to find it: How about extending the "for <X> in" syntax so that X can include default arguments ? This would be very...
23
by: Carter Smith | last post by:
http://www.icarusindie.com/Literature/ebooks/ Rather than advocating wasting money on expensive books for beginners, here's my collection of ebooks that have been made freely available on-line...
19
by: Nicolas Fleury | last post by:
Hi everyone, I would to know what do you think of this PEP. Any comment welcomed (even about English mistakes). PEP: XXX Title: Specialization Syntax Version: $Revision: 1.10 $...
4
by: Jeremy Yallop | last post by:
Looking over some code I came across a line like this if isalnum((unsigned char)c) { which was accepted by the compiler without complaint. Should the compiler have issued a diagnostic in this...
177
by: C# Learner | last post by:
Why is C syntax so uneasy on the eye? In its day, was it _really_ designed by snobby programmers to scare away potential "n00bs"? If so, and after 50+ years of programming research, why are...
4
by: Bob hotmail.com> | last post by:
Everyone I have been spending weeks looking on the web for a good tutorial on how to use regular expressions and other methods to satisfy my craving for learning how to do FAST c-style syntax...
3
by: Manuel | last post by:
I'm trying to compile glut 3.7.6 (dowbloaded from official site)using devc++. So I've imported the glut32.dsp into devc++, included manually some headers, and start to compile. It return a very...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.