Select query problems with dates

I am simply wanting to select records from an Access DB called 'notes' via VB6. The DB 'notes' has a table called 'notes' which has a field called 'caldate' with a data type 'date'.

I have declared the following date variable in VB:

Public caldatevar As Date

I believe Access stores dates in the format 12/30/2000, so assume I will provide the date in that format eg today:

caldatevar = #2/6/2000#

I am then having problems with my select statement. I have tried the following each with their resultant error:

"SELECT * FROM [notes] where caldate = caldatevar"          
=>too few parameters

"SELECT * FROM [notes] where caldate = '" & caldatevar & "'"    
=>data type missmatch

"SELECT * FROM [notes] where caldate = #2/6/2000#"      
=>this works

Can you put me right?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try the convert statement:
"SELECT * FROM [notes] where caldate = CONVERT(smalldatetime, '" & Format(caldatevar,"dd-mm-yyyy") & "')"

I'm not 100% sure about the syntax, but else see MSDN !

Also: you might not have to use the format statement, try it also without!


the # are the correct syntax for dates. You can code it with a variable as:

"SELECT * FROM [notes] where caldate = #" & Format(caldatevar,"dd-mm-yyyy")  & "#"

You can also use the BETWEEN clause:

"SELECT * FROM [notes] where caldate BETWEEN #" & Format(caldatevar,"dd-mm-yyyy")  & "# and #" & Format(caldatevar + 7,"dd-mm-yyyy")  & "#"


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
>>I believe Access stores dates in the format 12/30/2000,

Access stores dates in a double datatype variable (just like VB). What you see when you browse the table from Access use the registry settings for formatting.

I am faced with the date format almost everyday because I live in Quebec (Canada) and in french, we use dd/mm/yy which is sometimes interpreted like mm/dd/yy by database engines.

What I propose is that you transform you date to yyyy/mm/dd. This way, Access will always read your date correctly.

"SELECT * FROM [notes] where caldate = #" & format(caldatevar, "yyyy/mm/dd") & "#"      

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Hello ncw,

MS Access internal date format is"mm/dd/yyyy". However it shows the date values as you have defined in Windows Regional Settings.

You should format your date value and/or use CDate function in your SQL sentence. Here are some examples

Example 1:
"SELECT * FROM [notes] where caldate = #" & Format(MyDate,"mm/dd/yyyy") & "#"

Example 2:
"SELECT * FROM [notes] where caldate = CDate(" & MyDate & "')"

"SELECT * FROM [notes] where Format$(caldate,'yyyymmdd') = '" & Format$(MyDate,"yyyymmdd") & "'"

1st example is most efficient, because there's no needed variable type conversion done by JET.

CDate function is quite smart, because it tries to recognize your date mask.

?  cdate("1/15/2000")

?  cdate("15/1/2000")

?  cdate("2000/1/15")

Éric MoreauSenior .Net ConsultantCommented:

Have you tried with a day <= 12?

Here's the result:

?  cdate("1/10/2000")

?  cdate("10/1/2000")

?  cdate("2000/1/10")

So cDate is not the answer to all date problems!
ncwAuthor Commented:
Thank you all for your answers. The answer from mark2150 was the first to solve my problem.

I had got my dd's and mm's round the wrong way and also my concantenation of # had not been correct.

Actually access stores dates internally as a *REAL*. The date is the integer part and the time is the decimal part. The choice of format is completely cosmetic and depends on the locale settings.

Myself? I just use an unambiguous format like DD-MMM-YYYY that produces the no-confusion-possible output 06-FEB-2000. This will work everywhere no matter what the locale settings are.

I said it's quite smart (not very smart), I was going to warn about days < 13 but forgot it.. good you mentioned that! ;)

I guess I was using wrong word "internally", I meant "dd/mm/yyyy" works with both US regional settings and Finnish ( which I normally use.

Best regards,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.