Access & Dates makes my head hurt.

[Comfy]

I'm extracting records to a recordset and looping through each record.

I would like clarification on something as I'm not quite sure I have the correct Query.

My Table contains a DateTime Column formatted as EU General dd/mm/yyyy hh:nn:ss (We'll use 03/05/2015 00:00:01 for this example)

I'm using this DateTime in a sub query.

SQLstr = "Select * From Table2 Where [dDate] >= #" & rSet!DateTime &"#;"

which becomes "Select * From Table2 Where [dDate] >= #03/05/2015 00:00:01#;"

Due to Access' assumption that this is a US date format mm/dd/yyyy hh:nn:ss I get the incorrect results.

To fix this I have used "Select * From Table2 Where [dDate] >= CDate('" & rSet!DateTime &"');"

which becomes "Select * From Table2 Where [dDate] >= CDate('03/05/2015 00:00:01');"

But I am not sure that this is correct.

Do I have to use DateSerial to build the correct date or would formatting the Date to mm/dd/yyyy be sufficient?

 

[Norie]

You could try formatting to yyyy-mm-dd, which is, I think, the international standard date format.

SQLstr = "Select * From Table2 Where [dDate] >= #" & Format(rSet!DateTime, "yyyy-mm-dd") &"#;"

 

[mole999]

try using dd/mmm/yyyy, if pulled together correctly you could have 'apr 04 2015' or '04 apr 2015' and should avoid the 2 jan / 1 feb issues

 

[Darren Bartrup]

How about

"Select * From Table2 Where CLNG([dDate]) >= CLNG('" & rSet!DateTime &"');"

or would there still be the problem that it will be converted to the wrong date in long format? I'm sure I've used this before somewhere.

 

[Norie]

I'm not sure if ADO will like VBA conversion functions like CDate and CLng, assuming ADO is being used of course.

 

[Comfy]

Thanks Guys.

I've gone with formatting the Date column to use a Long Month so that I can be sure it's correct.

SELECT *
FROM Table1
WHERE [dDate] >= #05 Mar 2015#
AND [dDate] < #06 Mar 2015#;

 

[mole999]

ages since i played with access have a read here SQL BETWEEN Operator

 

[Comfy]

ages since i played with access have a read here SQL BETWEEN Operator

Thanks, my statement was just for example purposes

 

[xenou]

Date fields in Access are numbers, not text, as regards the underlying data stored in the table. Formatting should be irrelevant. It's curious what your exact problem is but I would suggest if you are using date literals in your queries to use internationally-aware date strings such as 01-Jan-2015 or Jan 1, 2015. IMHO on both sides of the atlantic we should stop using d/m/y and m/d/y -- agreeing to disagree isn't cutting it. You should also verify that the dates stored in Access are actually what you think they are (i.e., that 6/5/2015 is really 06-May-2015 - perhaps by parsing it into year, month, day or some similar technique. You probably should remain suspicious about *VBA* which is USA date-centric, but *Access* should follow your O/S locale settings. Also I would never hesitate to use DateSerial in queries to be sure as can be ... Year(date), month(date), day(date) is entirely unambiguous.

 

[Comfy]

Thanks Xenou.

I used dd MMM yyyy in the Table so that I can be sure the Dates are correct.

I'm confident now that my Query and results are accurate.