Date Format: mm/dd/yyyy versus dd/mm/yyyy (1 Viewer)

wilderfan

Registered User.
Local time
Today, 03:01
Joined
Mar 3, 2008
Messages
172
I'm using Date Picker to input a date on a form and I've just noticed something odd.

The property sheet for this control shows the following format: mm/dd/yyyy

I'm happy with that formatting and that is how the dates are ultimately stored in the table. BUT ...

When selecting the date from date picker - that is, while in the form - the date appears as: dd/mm/yyyy

Is there any way to get the date appearing on the form to match the desired formatting I have selected in the property sheet? I'm surprised that the two don't match.

(PS I'm using ACCESS 2013)
 

ChrisO

Registered User.
Local time
Today, 21:01
Joined
Apr 30, 2003
Messages
3,202
>>I'm happy with that formatting and that is how the dates are ultimately stored in the table.<<
If that is correct then you are storing the ‘Date’ as Text and it should be a Date/Time or Double field data type.

I can’t help with your problem because I don’t have Access 2013 and therefore don’t have the date picker. So I can’t test it.

However…

Technical point 1:
Dates in Access are stored as an IEEE 64 bit floating point number.
That number represents a number of days, not a date or time.
It only becomes a date/time when added to a base date.
The base date is the first instance of the 30 December 1899.

Technical point 2:
When processed, the number of days is split at the decimal separator.
The integer part can be positive or negative but the fractional part is only ever positive.
Both sides of the decimal separator are then added to the base date separately.

Technical point 3:
What we see directly in the Table has already been processed by Windows/Access.
What we see directly in the Table is dependent on our Regional Setting.

Technical point 4:
If you wish to look directly at the stored data you need to use the CDbl() function on the Date. Since the stored data is already in IEEE floating point format then all the CDbl() function needs to do is to tell Access not to format the data as Date.

Hope that helps.

Chris.
 

RainLover

VIP From a land downunder
Local time
Today, 21:01
Joined
Jan 5, 2009
Messages
5,041
I don't know the answer either. However I would suggest you create an addition Text box and see if you can format that to get the result you want.

If successful delete the original and use the new one.

PS It is a good idea to include your location somewhere so we have an idea as to what Regional Settings you have. Especially with questions like this.
 

nanscombe

Registered User.
Local time
Today, 10:01
Joined
Nov 12, 2011
Messages
1,082
You might wish to check your location in (Control Panel) Region and Language.

I'm only using Access 2010, but I would have thought the Language and Format settings were global.

I'm in the UK so have my Format set to English (United Kingdom) and the date picker shows up as dd/mm/yyyy. If I change my Format to English (United States) the date picker gives dates in mm/dd/yyyy format.
 

wilderfan

Registered User.
Local time
Today, 03:01
Joined
Mar 3, 2008
Messages
172
Location is Vancouver, Canada.

The file I'm working on has 2 controls that use Date Picker (on different forms).

In one case, clicking on a calendar date (but before hitting enter), the format is mm/dd/yyyy; but in the other case, the formate is dd/mm/yyyy.

I've looked at the formatting of the controls on the property sheets and the formatting says mm/dd/yyyy. Same formatting is used for the table fields.

So this is odd.

I will try to delete the offending control and set it up again. Hopefully that will help.
 

wilderfan

Registered User.
Local time
Today, 03:01
Joined
Mar 3, 2008
Messages
172
Thanks, Nigel.

In the Control Panel, I had set it to English (Canada).

Once I changed it to English (U.S.), the Date Picker then displayed the format that I prefer (which happens to be the American format).

Now both of my Date Picker controls show the same (U.S.) format.

However, I am still puzzled why the 2 controls were behaving differently when my Control Panel setting was set to English (Canada). Very strange.
 

Users who are viewing this thread

Top Bottom