Date format and regional settings (A2K SP3)

Topic

New Reply

When my Access program was run under Windows 98, the date format is dd/mm/yyyy, with the day and month displayed in 2 digits, say 05/12/2002. But the same program when run under Windows XP with the same date format, it was displayed as 5/12/2002. How could I ensure that the day and month always displayed in 2 digits even if it is a single-digit numerical day and month?

Another related question, can I force user to enter the date in the format dd/mm/yyyy without consideration of the regional settings under the Control Panel? For example, if an user has the regional date setting as mmm/dd/yyyy, can I display my date as well as enforce its entry as dd/mm/yyyy?

Reply | Quote

Replies

Are you talking about the system date format or the display format for your form or for a table? Access ignores the regional date setting for dd/mm/yy in tables if you have the field format set to general date, except to use the delimiters set there. General Date and Medium date are just handy labels that hide the actual format string being used. If you switch to a different locale in Regional Options and go back and look at those forms, you’ll see a hard coded format string in there that probably needs changing to match the current locale’s format. We run into this in our applications and have learned to handle that in code.

You can display your date by using a specific format string in the display format of a field or control. The only way you could force them to enter it that way, though, is with an input mask. I wouldn’t recommend that because the date format isn’t standard everywhere, although that sequence is consistent except in the USA. We use the medium date format in our apps because it can easily be understood anywhere in the world and doesn’t choke on something like a period as the delimiter in a date, which is what is used in some countries. They can still enter the date in the regional format they prefer, but the control will display it as a medium date, giving them immediate feedback in case they got their days and months mixed up.

Reply | Quote

I am sorry my posting isn’t very clear. What I want is that all my date entries in my forms as well as in my reports are consistently displayed in dd/mm/yyyy format. I also want my users to key in the dates in the same format (including the delimiters) to prevent confusion. The date dd/mm/yyyy is the “official” national format. My program is only used locally and I can ignore locale differences in displaying dates. However, I have users from different cultural and educational backgrounds and who may set their locales differently. More likely they use their computers with settings made by the vendor without realizing the display differences because of locale setting. I have come across a few locally developed Access applications and all of them require users to make the correct Regional Settings before starting and I really find them a bother.

Based on your leads, I made the following changes to all my date input and display boxes as below:

display format – dd/mm/yyyy (my original format is Short Date)
input mask – 00/00/0000;0;_

It apparently worked okay after I tested it with different locales in my system. Please tell me in case you foresee any possible caveat in my method. Thanks Charlotte.

Reply | Quote

Just be careful of any SQL that uses dates in that format. SQL requires the US date format of month/day/year, not matter how you store the date in the database. SQL will also have problems with numeric formats that use comma for the decimal delimiter and anything except a comma as the digit grouping delimiter. I ran into that issue with French settings, which use a period as the date delimiter and a comma for the decimal delimiter, with spaces for digit grouping. It isn’t just SQL statements that have the problem, either. Functions like DLookup result in a SQL expression underneath, so they need the dates and numbers in a format SQL can understand as well.

Reply | Quote

Charlotte, I discover something very peculiar.

I am running A2K SP3 under Windows XP SP1. My regional option is English (United States) and in the Customize Regional Options for Date, the short date format is M/d/yyyy (the default).

The Access textbox for my date entry has the following properties:

display format – dd/mm/yyyy
input mask – 00/00/0000;0;_

When my date entry is say 16/08/1998, it is displayed as such and my program tells me it is day 16, month 8, year 1998. When I moves the focus to the next entry, the date display remains unchanged as 16/08/1998. When I click on the date input box again, the date display remains the same as 16/08/1998.

However when the date entry is 06/08/1998, my program tells me it is day 8, month 6 and year 1998, and when I moves to the next entry, the date display changes to 08/06/1998. When I put the date input box in focus again by clicking on it, it changes to 6/8/1998.

It appears that when the day entry (the first two digits) is more than 12, there is no problem. The problem appears only when the day is 01 to 12 and then Access would take it as month value. The short date format in the regional setting appears to interfere with Access date box display format.

Can you explain this peculiarity? I am out of my wits.

Reply | Quote

What you’re encountering is because you’re running under English (United States) regional settings. You can’t force data entry into another date format and have Access/Windows understand what you mean. The input mask just specified numbers, not the meaning of the numbers. There is no ambiguity in the 13th of a month because there is no matching month, but Access can’t tell whether 02/01/03 is January 2 or February 1, except by using the regional settings to decide. That is one of the reasons we use the medium date, because it stays the same in all the regional settings and never confuses Access. We ask the user to enter the dates in dd-mmm-yy format and display them the same way. We do not use input masks, so the user is still able to enter a short date in the format he is used to if he really wants to and Access will interpret it properly based on his regional settings.

Reply | Quote

Excuse my naivete, doesn’t the date display format – dd/mm/yyyy “clearly” tell Acces that the first two digits stand for days, and the next two for months? If not, what does the display format signify?

Reply | Quote

The format is for display of a value. It assumes the values are stored in the order you have formatted them but is smart enough to know that any number above 12 must be day rather than a month. The format doesn’t have anything to do with the input mask. They’re two entirely separate things.

Reply | Quote