Dates in vb/sql criteria (MDY, DMY)

[pondlife]

In ACC2000 on a PC set up for UK time/date format (DMY) how should you include a VB date variable in a SQL criteria statement? I'm finding that when dteTestDate is 12May it is evaluated in "SELECT * FROM tblMyTable WHERE SourceDate = " & #dteTestDate# & ";" as 05Dec. I've now learned that Access SQL uses MDY, but can't come up with a slick solution.

 

[RuralGuy]

I think this link should help a bit.

 

[ecawilkinson]

hi,
format dteTestDate as the Access internal number and test against that. Try:
SELECT * FROM tblMyTable WHERE SourceDate = " & CLng(dteTestDate)

This worked when dteTestDate was a Date variable rather than a string type. If dtetestDate is a string then use CLng(CDate(dteTestDate)).

As this uses the internal date representation, the format type used is of no consequence. Since discovering this trick, I have always handled dates in this way and had no problems.

HTH,
Chris

 

[pondlife]

Thanks Rural Guy, Allen Browne's funtion worked immediately for me - saved me a lot of hassle working it out for myself! A great help.

 

[pondlife]

Thanks Chris. That looks like a good solution too, and needs no special function so would be neater.

 

[RuralGuy]

Glad we could help.

Chris: that is a neat trick but shouldn't it be:
CLng([SourceDate]) = " & CLng(dteTestDate)
to eliminate any hour data in the source?