Solved Problem with date range defined by a textbox (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:24
Joined
Feb 19, 2002
Messages
42,970
I didn't notice anyone mention this so I will add it:

When you use unbound controls for your SQL date range, you MUST define them as date data types. This will prevent Access from treating the control as a string and confusing your date order. Is 2/1/21 February 1 or January 2? Depends on what side of the pond you are on. SQL assumes all ambiguous dates are mm/dd/yyyy and that causes a lot of problems for people outside of the US where they have a different opinion of how a date should be displayed. But that's just it. The date format is strictly for human consumption. Internally (including controls on forms if they are defined as dates) the data is actually a double precision number with the integer value being the number of days since Dec 30, 1899 and the decimal is the time since midnight.

If your queries ALWAYS reference date data types (including references to a control defined as a date or bound to a date/time field), all will be well. If you reference a control not defined as a date or you build the SQL string in code, you will have to convert your string date format to mm/dd/yyyy or yyyy/mm/dd to get it to work correctly in SQL.
 

Users who are viewing this thread

Top Bottom