Bad Dates - mm/dd/yy vs dd/mm/yy (1 Viewer)

[widemonk]

Using Access 2013 I have a form of continuous records with a custom search/filter text boxes in the form header .
In the date fields I have purposefully formatted the display of the dates as dd-mmm-yyyy for the avoidance of any doubt so 01/10 shows as 01-Oct instead of 10-Jan.

However when inputting the fields, the dates are still being interpretted as mm/dd where possible and only dd/mm where the first digit is over 12 and therefore cant represent the month.

In the examples below,
Pic2 shows 02-Jan but because the record for 21-Jan is missing, the system is actually reading that as 2/1, or Feb 1st.
Pic3 shows 10-Jan but because ALL records are missing, the system is actually reading that as 10/1, or Oct 1st.
Pic4 shows all correct because there is no 17th month so must be 17-Jan.

Format(txtPurchFrom, "dd/mm/yyyy") is used in the VBA that defines the custom SQL filter for the query.

Where else on earth is the date being interpreted as potential mm/dd ??

 

[jdraw]

ALWAYS use mm/dd/yyyy for inputting dates regardless of your regional settings

 

[theDBguy]

Where else on earth is the date being interpreted as potential mm/dd ??

Interpretation only happens in code and expressions. Bound controls follow your regional settings.

 

[sonic8]

ALWAYS use mm/dd/yyyy for inputting dates regardless of your regional settings

This is extremely bad advice!
With my regional settings this will happen:

? Format(CDate("08/10/2022"), "YYYY-MM-DD")
2022-10-08

You probably meant to suggest to use mm/dd/yyyy as date format in query SQL, which makes sense even though I prefer the ISO format, which also works reliably.

 

[MajP]

Where else on earth is the date being interpreted as potential mm/dd ??

Since it is an unbound textbox, access does some guessing for you. All items in a textbox are variants. But since you have formatting in the field it knows to treat the values as a date. So you can use and enter based on regional settings.

The problem happens in your vba. In vba sql you should create a properly delimited string in either the format @jdraw or @sonic8 show. Regardless of your regional settings

2 Jan 2021 as
#1/2/2021#
or
#2021-2-1

If using the vba filter then

dim fromDate as string
dim toDate as string
fromDate = "#" & format(txtFrom,"MM/DD/YYYY") & "#"           'or "YYYY-MM-DD"
toDate =  "#" & format(txtTo,"MM/DD/YYYY") & "#"
Me.Filter = "PurchaseDate Between " & FromDate & " AND " & ToDate

if Sql

dim fromDate as string
dim toDate as string
fromDate = "#" & format(txtFrom,"MM/DD/YYYY") & "#"           'or "YYYY-MM-DD"
toDate =  "#" & format(txtTo,"MM/DD/YYYY") & "#"
strSql = "Select .... where purchaseDate between " & fromdate & " AND " & toDate

 

[Gasman]

I found this years ago when I was using Access, and used it until I stopped using Access.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[MajP]

I use this to delimit strings, dates, nulls.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Pat Hartman]

Dates are not stored as strings. They are stored as double precision numbers with 0 (for Access) being 12/30/1899. The decimal part is the fraction of a day since midnight.

print cdbl(Now())
44800.6342361111
print now()
8/27/2022 3:13:24 PM

NEVER, format a date when you need to work with it as a date. Formatted dates are for human consumption, not for computer processing. If you want to compare two dates, compare them by name:

Where TranStartDT <= Date()

No conversion, no confusion. Period.

Where you run into trouble is in two places.
1. unbound controls. If you want them to hold a date and display it using your regional settings, set the format of the control to Short Date (or whatever standard format you want)
2. When you are building an SQL String. In this case you have no option but to convert a date field to a string and because SQL Server will always assume the date format to be mm/dd/yyyy unless it is some unambiguous format such as yyyy/mm/dd or dd-mmm-yyyy*.

You can create a querydef that looks like this:
Select ... From ... Where SomeDate > Forms!yourform!SomeDate
and there is no need to format the form field date assuming that the SomeDate control is either bound to a datetime data type field or is unbound but has a date format so that Access "knows" the control will hold a date.

HOWEVER, if you build your SQL in code, you will be creating an SQL STRING and when the date is a STRING, It MUST be either mm/dd/yyyy or the unambiguous yyyy-mm-dd

So
strSQL = "Select ... From ... Where SomeDate > #" & Format(Me.SomeDate, "mm/dd/yyyy") & "#"

*Although this seems like the ideal situation for formatted dates, you run into language issues so don't do it if you will ever distribute the app in two different language areas.

 

[sonic8]

[...] SQL Server will always assume the date format to be mm/dd/yyyy [...]

You really mean SQL Server? The above is correct for JET/ACE/Access SQL, but it is not for SQL Server.

SET DATEFORMAT dmy;
SELECT convert(datetime, '08/10/2022');
Result: 2022-10-08

 

[gemma-the-husky]

@widemonk

How are you converting the date on the screen to a date in your SQL? That must be the problem.

 

[Gasman]

Where else on earth is the date being interpreted as potential mm/dd ??

Right here.

Format(txtPurchFrom, "dd/mm/yyyy") is used in the VBA that defines the custom SQL filter for the query.

 

[Pat Hartman]

@sonic8 and @Gasman Are you saying that SQL Server will interpret #01/02/2022# as Feb 2 and not Jan 1? When did that change?

 

[Gasman]

@sonic8 and @Gasman Are you saying that SQL Server will interpret #01/02/2022# as Feb 2 and not Jan 1? When did that change?

Not I, I have never used SQL server with Access.
I was responding to the O/P's query that I quoted. AFAIK the O/P never mentioned SQL server?

 

[Pat Hartman]

I meant to just say SQL but "server" came out also