By default Access manipulates dates in US format mm/dd/yyyy.
As you can imagine this is very confusing for any other locale.
This is where it gets silly - Any Date presented between # # delimiters are assumed to be in mm/dd/yy format, UNLESS it can't be e.g. #23/11/23#
In this case Access "helpfully" understands that it must be the 23-Nov-2023. You can see this effect in the query builder and the immediate window.
The best way to circumvent this behaviour is to create a function that forces all dates into a unambiguous format when you are building SQL statements- and I prefer #yyyy-mm-dd# which works in every locale and on SQL server if you ever go there.
This is a variant of the Allen Browne function:
Code:
Function ServerDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
ServerDate = Format$(varDate, "'yyyy-mm-dd'")
Else
ServerDate = Format$(varDate, "'yyyy-mm-dd hh:nn:ss'")
End If
End If
End Function
The other benefit of this format is that during debugging, there is no misunderstanding or wondering what format the date being passed around is in.