- Local time
- Today, 11:14
- Joined
- Feb 19, 2002
- Messages
- 44,863
A lot of work when you should have just fixed the underlying problem and created a date field.
I pointed out earlier how dates were stored internally and that goes for SQL Server as well as Jet/ACE. They are NOT stored as strings. When you use dates as strings, they act like strings. What a surprise
As a string,
#9/25/2022# is GREATER THAN #10/1/2022# That is why we use actual date data types when working with dates - so Access and SQL Server will handle them as dates
If you want to work with a string date, you need to use a yyyymmdd format where all three fields are fixed in length and the year is first, the month is numeric and second, the day is third.
Also, Just FYI, if you are not in the US, you will have the additional problem of Access "helping" you by converting ambiguous dates like 01/02/2022 to mm/dd/yyyy rather than using your local default of dd/mm/yyyy whenever the date is a STRING. The issue does not arise if you carefully work only with date data type fields or use the unambiguous yyyy/mm/dd format.
I pointed out earlier how dates were stored internally and that goes for SQL Server as well as Jet/ACE. They are NOT stored as strings. When you use dates as strings, they act like strings. What a surprise
As a string, #9/25/2022# is GREATER THAN #10/1/2022# That is why we use actual date data types when working with dates - so Access and SQL Server will handle them as dates
If you want to work with a string date, you need to use a yyyymmdd format where all three fields are fixed in length and the year is first, the month is numeric and second, the day is third.
Also, Just FYI, if you are not in the US, you will have the additional problem of Access "helping" you by converting ambiguous dates like 01/02/2022 to mm/dd/yyyy rather than using your local default of dd/mm/yyyy whenever the date is a STRING. The issue does not arise if you carefully work only with date data type fields or use the unambiguous yyyy/mm/dd format.
