Sigh - Access Regional Dates (1 Viewer)

bigal.nz · Aug 14, 2017

Hello,

I understand access tries to be smart with dates. Here in the south pacific we use dd/mm/yyyy.

The net result is access is not smart and across my forms I seem to have a bunch of dates that end up in US format and if it gets a date where it "wont compute" for mm/dd/yyyy then it will put it in dd/mm/yyyy.

How to I override this behaviour?

Lord knows what the fix will do to the existing data.

Thanks

Al

isladogs · Aug 14, 2017

This is one of the most complained about 'features' of Access by those of us not in the USA. Here in the UK we also use dd/mm/yyyy

If you search you will find plenty of similar posts here on this forum
The problem is made worse by Access interpreting some dates correctly but not others

However, dates in forms and reports should always be displayed correctly based in the default date format set on your computer

The problem comes with SQL code

Today 14/08/2017 is correctly seen by Access as 14th August (as it can't be anything else)
But 10/08/2017 becomes 8th Oct being converted to mm/dd/yyyy

In order to be certain of getting the result you require ALWAYS use Format([DateField],"mm/dd/yyyy") in SQL code to ensure Access treats the date correctly

For example:

Code:

strDateFilter = " AND DetentionDate Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & _
        "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "#"

The 'fix' doesn't change the underlying data which is in your default format of dd/mm/yyyy

Formula2 · Aug 14, 2017

Hopefully this will work for you.
Go to Control Panels, Region and choose MM/dd/yyyy for Short Date
Make a new module called ShortDateSetting
Paste the following code into the new module.

Option Compare Database
Option Explicit

Public Const LOCALE_SSHORTDATE = &H1F
Public Declare PtrSafe Function GetSystemDefaultLCID Lib "kernel32" () As Long
Public Declare PtrSafe Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Boolean

Function ChangeSetting()
Dim lngLocale As Long

lngLocale = GetSystemDefaultLCID()
If SetLocaleInfo(lngLocale, LOCALE_SSHORTDATE, "MM/dd/yyyy") = False Then
MsgBox ("A problem has been encountered in reseting the date.")
End If
End Function

isladogs · Aug 14, 2017

@formula2

Why on earth would the OP want to change his default computer date from the New Zealand default of dd/mm/yyyy to the US version of mm/dd/yyyy?

ashleedawg · Aug 14, 2017

"dang regional date settings"... déjà vu

(Just to mess things up, I prefer the 'none of the above' setting of yyyy/mm/dd specific to my region of a 3-foot circle around me.

Makes sense to me since it can be sorted as a date, text, or numbers, with the same result.)

isladogs · Aug 15, 2017

ashleedawg said:
Just to mess things up, I prefer the 'none of the above' setting of yyyy/mm/dd specific to my region of a 3-foot circle around me.

Makes sense to me since it can be sorted as a date, text, or numbers, with the same result.)[/SIZE]

Hey - a 3 foot circle - good to know you're getting out a bit more these days!

I agree however - it's a good alternative as is yyyy-mm-dd
In fact almost anything is better than mm/dd/yyyy as a default format

bigal.nz · Aug 15, 2017

ridders said:
This is one of the most complained about 'features' of Access by those of us not in the USA. Here in the UK we also use dd/mm/yyyy

If you search you will find plenty of similar posts here on this forum
The problem is made worse by Access interpreting some dates correctly but not others

However, dates in forms and reports should always be displayed correctly based in the default date format set on your computer

The problem comes with SQL code

Today 14/08/2017 is correctly seen by Access as 14th August (as it can't be anything else)
But 10/08/2017 becomes 8th Oct being converted to mm/dd/yyyy

In order to be certain of getting the result you require ALWAYS use Format([DateField],"mm/dd/yyyy") in SQL code to ensure Access treats the date correctly

For example:

Code:

strDateFilter = " AND DetentionDate Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & _ "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "#"

The 'fix' doesn't change the underlying data which is in your default format of dd/mm/yyyy

Thanks. So I have a datasheet based on a query.

The query looks like:

Code:

strSQL = SELECT MAS.ID AreaNumber, tifDate, assignmentDate, status FROM MAS WHERE assignmentDate = (SELECT MAX(assignmentDate) AND status = 'Complete';

So the date fields references in the SELECT part of the statement are OK? But the dates in the where clause should read:

Code:

....assignmentDate = (SELECT MAX (format(assignmentDate, "mm/dd/yyyy"))....

Just one question - did you mean to type mm/dd/yyyy in the format? dont I want dd/mm/yyyy ?

Cheers

Al

Galaxiom · Aug 15, 2017

Literal dates in the SQL must be formatted as explained. However the engine already understands the dates referred to by table fields so there is no need to change the format in those cases. Functions are applied directly to the field.

isladogs · Aug 15, 2017

@bigal.nz

Adding to Galaxiom's reply:

Just one question - did you mean to type mm/dd/yyyy in the format?

Yes I DID mean EXACTLY that
This is so Access will ALWAYS interpret the dates correctly.
The actual dates are stored in YOUR default format

Try creating a query in DESIGN view something like this:

Code:

SELECT * FROM tblDateFields WHERE DateField Between #03/01/2017# And #11/07/2017# ORDER BY tblDateFields.DateField;

and run it - the dates are for the range you'd expect - 3 Jan to 11 July

Now change the query to SQL view.
It has been changed by Access to mm/dd/yyyy format for SQL view:

Code:

SELECT * FROM tblDateFields WHERE DateField Between #1/3/2017# And #7/11/2017# ORDER BY tblDateFields.DateField;

The latter is what you would need to use if writing the code in the VBE

Go back to Design view & as if by magic its back to what you typed originally

P.S. Galaxiom has some brilliant examples of Access misinterpreting dates wrongly written in dd/mm/yyyy format for SQL.
e.g. if I remember right something in 1929?

Galaxiom · Aug 15, 2017

ridders said:
The actual dates are stored in YOUR default format

No. Dates are always stored as Double Precision Floating Point numbers regardless of the regional settings. The integer part is the number of days from 30 December 1899 and the decimal part is the time as fractions of a day.

The displayed values are converted from the number to the regional date format.

isladogs · Aug 15, 2017

True - I was being simplistic in describing how they will appear to the user.

Anyway its late here in the UK: 15/08/2017 03:09:57 or as stored in Access 42962.1319097222

Now what was that bizarre date example in 1929?

Galaxiom · Aug 15, 2017

ridders said:
TNow what was that bizarre date example in 1929?

Anything where the date is invalid as both mm/dd/yy and dd/mm/yy but can be interpreted as yy/mm/dd.

eg 29/2/17

2017 is not a leap your so there is no 29 Feb

bigal.nz · Aug 15, 2017

ridders said:
@bigal.nz

Adding to Galaxiom's reply:

Yes I DID mean EXACTLY that
This is so Access will ALWAYS interpret the dates correctly.
The actual dates are stored in YOUR default format

Try creating a query in DESIGN view something like this:

Code:

SELECT * FROM tblDateFields WHERE DateField Between #03/01/2017# And #11/07/2017# ORDER BY tblDateFields.DateField;

and run it - the dates are for the range you'd expect - 3 Jan to 11 July

Now change the query to SQL view.
It has been changed by Access to mm/dd/yyyy format for SQL view:

Code:

SELECT * FROM tblDateFields WHERE DateField Between #1/3/2017# And #7/11/2017# ORDER BY tblDateFields.DateField;

The latter is what you would need to use if writing the code in the VBE

So short answer : if writing SQL in VBA use mm/dd/yyyy by applying the format statement?

isladogs · Aug 15, 2017

bigal.nz said:
So short answer : if writing SQL in VBA use mm/dd/yyyy by applying the format statement?

Yes

Sigh - Access Regional Dates (1 Viewer)

bigal.nz

Registered User.

isladogs

MVP / VIP

Formula2

New member

isladogs

MVP / VIP

ashleedawg

"Here for a good time"

isladogs

MVP / VIP

bigal.nz

Registered User.

Galaxiom

Super Moderator

isladogs

MVP / VIP

Galaxiom

Super Moderator

isladogs

MVP / VIP

Galaxiom

Super Moderator

bigal.nz

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread