Access VBA & SQL date formats

Question

I'm having problems with date format in an SQL statement

CurrentTime = Format(CurrentTime, "dd/mm/yyyy hh:mm")
SQL = "SELECT Count([ID]) AS Booked FROM [Appointment Slots] WHERE [Appointment Slots].Time=#" & CurrentTime & "#"

The bizarre thing here is that sometimes when I run the code this works. Other times, and without changing the code, it doesn't, but then it works when I change the date format to mm/dd/yyyy hh:mm then it works for a while then stops working until I change it back to dd/mm/yyyy hh:mm

Clearly something is going wrong with the regional date settings and how it's storing dates but I can't pin down a solution to this. Is there no way to compare a date in Access SQL that is independent of format?

A parameter query is a neat way to avoid date format issues, and also avoid needing to include # delimiters for date literals. If the query expects a date parameter, you just give it a date value. — HansUp
– HansUp, Commented Mar 10, 2016 at 15:11
This worked best of the solutions I tried, can you write it as an answer so I can mark it as accepted? — Peter Astbury
– Peter Astbury, Commented Mar 11, 2016 at 10:43

Gustav · Accepted Answer · 2016-03-10 14:50:21Z

4

You should make it a habit using the ISO sequence, and nn for minutes.

Also escape the "/" and ":" to have a true slash and colon, otherwise they will be replaced with the localized date and time separators:

CurrentTime = Format(CurrentTime, "yyyy\/mm\/dd hh\:nn")

This also works for ADO and FindFirst, which the "reverse" US format (mm/dd/yyyy) does not.

answered Mar 10, 2016 at 14:50

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HansUp Over a year ago

"otherwise they will be replaced with the localized date and time separators" Thanks for that. Would "yyyy-mm-dd" format also be subject to that localization substitution?

Gustav Over a year ago

No, and indeed not for dates where / and : are the only separator characters. Elsewhere, you also have the dot as the separator for milliseconds, but that is not handled natively by Access and VBA.

HansUp Over a year ago

Thanks again. I'd seen "yyyy\/mm\/dd" used before, but never understood why it was useful. Since I'm in U.S., I'm too ignorant about many localization details. ;-)

Gustav Over a year ago

I think it is in France they use a space as a separator. That can really cause strange happenings in SQL.

Peter Astbury Over a year ago

Thanks, this still didn't work however. Resetting my regional settings to US formats and then re-entering data in US format and the code worked. Also - the suggestion from HansUp in a comment on the OP of using parameter queries also seemed to work.

HansUp · Accepted Answer · 2016-03-11 14:38:32Z

1

Is there no way to compare a date in Access SQL that is independent of format?

Consider a query with a parameter in its WHERE clause, similar to this ...

WHERE [Appointment Slots].Time=[Enter Appointment Time]

You can also add a PARAMETERS clause at the beginning of your SQL statement, but it's not absolutely required ...

PARAMETERS [Enter Appointment Time] DateTime;

So when the query expects a Date/Time parameter, you just give it a Date/Time value.

A parameter query avoids date format issues, and also avoids the need for # date delimiters in the SQL statement.

edited Mar 11, 2016 at 14:38

answered Mar 11, 2016 at 14:24

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Comments

Sergey S. · Accepted Answer · 2016-03-10 13:14:52Z

1

Date format #....#, used in queries works correctly only with american date format mm/dd/yyyy

The best way for working with dates is passing them as parameters to query. In this case used internal format, which doesn't depend on regional settings.

edited Mar 10, 2016 at 13:14

answered Mar 10, 2016 at 13:08

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

1 Comment

Gustav Over a year ago

It also works with the ISO sequence. And using a slash only will cause the expression to be localized.

Collectives™ on Stack Overflow

Access VBA & SQL date formats

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related