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?

 

[Kyle123]

In Databases of whatever flavour, always use ISO dates (yyyy-mm-dd) it's universal and works everywhere. My personal opinion is that DB designers shouldn't recognise local variations as it just creates confusion (especially since MS is incorrectly under the impression that most of the world uses mm/dd)

 

[xenou]

I haven't found Access to be particularly good with yyyy-mm-dd as a format, sadly. I prefer dd-mmm-yyyy with Access.