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?
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.
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.
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.
