• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

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?
0
ncw
Asked:
ncw
  • 2
  • 2
  • 2
  • +2
1 Solution
 
rovermCommented:
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!

RoverM

0
 
mark2150Commented:
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")  & "#"

M
0
 
É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") & "#"      


0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
paaskyCommented:
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 & "')"

Example3:
"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")
15.1.2000

?  cdate("15/1/2000")
15.1.2000

?  cdate("2000/1/15")
15.1.2000

Regards,
Paasky
0
 
Éric MoreauSenior .Net ConsultantCommented:
Paasky,

Have you tried with a day <= 12?

Here's the result:

?  cdate("1/10/2000")
01/10/2000

?  cdate("10/1/2000")
10/01/2000

?  cdate("2000/1/10")
10/01/2000

So cDate is not the answer to all date problems!
0
 
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.

Thanks
0
 
mark2150Commented:
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.

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

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

Best regards,
Paasky
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now