Date Format and using database in various countries | | |
Hello all,
General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.
Should I save a date format in the table design, such as: mm/dd/yyyy?
What I've done for years is to store the date format in date fields, then on
the forms, based on their region, I would set the date formats on form_load
depending on their preference of mm/dd/yyyy or dd/mm/yyyy. To try to combat
confusion, I added a 3rd preferred date format of: mmm-dd.yyyy, where the
month will be 3 letter designations.
Just curious what others are doing that deal with a database that can be
used globally.
Many Thanks,
Andy
| | | | re: Date Format and using database in various countries
Internally, Access stores date/time values as real numbers, where the
integer part represents the date (number days since Dec 30, 1899) and the
time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)
At display time, Access uses the date format for the computer displaying the
value (unless you specify a format in the property of the field in table
design, or the text box on the form.)
For details on how to avoid the 3 cases where Access interprets your entries
incorrectly, see:
International Date Formats in Access
at: http://allenbrowne.com/ser-36.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ARC" <andy@andyc.comwrote in message
news:y4BGi.2723$ZA5.1474@nlpi068.nbdc.sbc.com... Quote:
Hello all,
>
General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.
>
Should I save a date format in the table design, such as: mm/dd/yyyy?
>
What I've done for years is to store the date format in date fields, then
on the forms, based on their region, I would set the date formats on
form_load depending on their preference of mm/dd/yyyy or dd/mm/yyyy. To
try to combat confusion, I added a 3rd preferred date format of:
mmm-dd.yyyy, where the month will be 3 letter designations.
>
Just curious what others are doing that deal with a database that can be
used globally.
>
Many Thanks,
>
Andy
| | | | re: Date Format and using database in various countries
Thanks for the link, Allen. I do use the format field for unbound date entry
(for critieria fields on a reports screen, for example).
The only thing I can't tell from your link is the answer to the question on
whether or not to save a date format in the table design for date fields. My
original thinking in doing so was in 1999, to make sure that access was
actually saving the year's as 4 digits. So I used mm/dd/yyyy in the format
in table design.
Not sure if I should be doing this anymore.
Thanks!
"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in message
news:46eb4d27$0$14171$5a62ac22@per-qv1-newsreader-01.iinet.net.au... Quote:
Internally, Access stores date/time values as real numbers, where the
integer part represents the date (number days since Dec 30, 1899) and the
time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)
>
At display time, Access uses the date format for the computer displaying
the value (unless you specify a format in the property of the field in
table design, or the text box on the form.)
>
For details on how to avoid the 3 cases where Access interprets your
entries incorrectly, see:
International Date Formats in Access
at: http://allenbrowne.com/ser-36.html
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"ARC" <andy@andyc.comwrote in message
news:y4BGi.2723$ZA5.1474@nlpi068.nbdc.sbc.com... Quote:
>Hello all,
>>
>General question for back-end database that has numerous date fields
>where the database will be used in regions that put the month first, and
>regions that do not.
>>
>Should I save a date format in the table design, such as: mm/dd/yyyy?
>>
>What I've done for years is to store the date format in date fields, then
>on the forms, based on their region, I would set the date formats on
>form_load depending on their preference of mm/dd/yyyy or dd/mm/yyyy. To
>try to combat confusion, I added a 3rd preferred date format of:
>mmm-dd.yyyy, where the month will be 3 letter designations.
>>
>Just curious what others are doing that deal with a database that can be
>used globally.
>>
>Many Thanks,
>>
>Andy
>
| | | | re: Date Format and using database in various countries
ARC wrote: Quote:
Thanks for the link, Allen. I do use the format field for unbound
date entry (for critieria fields on a reports screen, for example).
>
The only thing I can't tell from your link is the answer to the
question on whether or not to save a date format in the table design
for date fields. My original thinking in doing so was in 1999, to
make sure that access was actually saving the year's as 4 digits. So
I used mm/dd/yyyy in the format in table design.
>
Not sure if I should be doing this anymore.
Makes no difference to how it is stored (never did). What is stored is a
number.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com | | | | re: Date Format and using database in various countries
As Rick says, it is best not to use anything in the Format property of the
date/time field in your table, nor on the Format property of controls bound
to a date/time field.
That way your application will work in accordance with the user's settings
(in the Regional Settings of the Windows Control Panel.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ARC" <andy@andyc.comwrote in message
news:CQRGi.134$6p6.128@newssvr25.news.prodigy.net. .. Quote:
Thanks for the link, Allen. I do use the format field for unbound date
entry (for critieria fields on a reports screen, for example).
>
The only thing I can't tell from your link is the answer to the question
on whether or not to save a date format in the table design for date
fields. My original thinking in doing so was in 1999, to make sure that
access was actually saving the year's as 4 digits. So I used mm/dd/yyyy in
the format in table design.
>
Not sure if I should be doing this anymore.
>
Thanks!
"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in message
news:46eb4d27$0$14171$5a62ac22@per-qv1-newsreader-01.iinet.net.au... Quote:
>Internally, Access stores date/time values as real numbers, where the
>integer part represents the date (number days since Dec 30, 1899) and the
>time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)
>>
>At display time, Access uses the date format for the computer displaying
>the value (unless you specify a format in the property of the field in
>table design, or the text box on the form.)
>>
>For details on how to avoid the 3 cases where Access interprets your
>entries incorrectly, see:
> International Date Formats in Access
>at:
> http://allenbrowne.com/ser-36.html
>>
>"ARC" <andy@andyc.comwrote in message
>news:y4BGi.2723$ZA5.1474@nlpi068.nbdc.sbc.com.. . Quote:
>>Hello all,
>>>
>>General question for back-end database that has numerous date fields
>>where the database will be used in regions that put the month first, and
>>regions that do not.
>>>
>>Should I save a date format in the table design, such as: mm/dd/yyyy?
>>>
>>What I've done for years is to store the date format in date fields,
>>then on the forms, based on their region, I would set the date formats
>>on form_load depending on their preference of mm/dd/yyyy or dd/mm/yyyy.
>>To try to combat confusion, I added a 3rd preferred date format of:
>>mmm-dd.yyyy, where the month will be 3 letter designations.
>>>
>>Just curious what others are doing that deal with a database that can be
>>used globally.
| | | | re: Date Format and using database in various countries
I do use date input masks however, to make date entry easier. I let the user
pick the default date entry format. Any problems with this?
Thanks guys!
| | | | re: Date Format and using database in various countries
That's a question of style
Personally I don't use input masks. They do nothing to ensure the date
format is correct, and they actually slow users down. A good data entry
operator knows they can enter dates for the current year with just 3 -5
keystrokes, e.g.:
1/1
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ARC" <andy@andyc.comwrote in message
news:KlSGi.9483$924.3830@newssvr23.news.prodigy.ne t... Quote:
>I do use date input masks however, to make date entry easier. I let the
>user pick the default date entry format. Any problems with this?
>
Thanks guys!
| | | | re: Date Format and using database in various countries
Dates are a serious issue in Access especially down under in
Australia!
We use the british system and cannot figure out why access uses the mm
\dd\yyyy format sometimes.
The way we always do vba is to use MMM where applicable.
That way there is no doubt as to the date...
This works against sql server as well so it is more universal than the
function to switch at date to the american format above.
No offense meant to Allen but just use format to make your month three
characters and life is easier!
e.g. format(MyDate(),"dd-mmm-yyyy") works with no issues against any
system....for australian/UK users and even using format(mydate(),"mmm-
dd-yyyy") for us systems works fine..
Note: the format - yyyy-mm-dd is one of the best for queries against
other databases - e.g. dbII or sql server......and most systems
shouldn't have an issue with this.....
Regards,
Tom Bizannes
Sydney, Australia
| | | | re: Date Format and using database in various countries
SmartbizAustralia wrote: Quote:
Dates are a serious issue in Access especially down under in
Australia!
>
We use the british system and cannot figure out why access uses the mm
\dd\yyyy format sometimes.
>
The way we always do vba is to use MMM where applicable.
>
That way there is no doubt as to the date...
Except in a foreign language version where the month abbreviations are not the
same. Stick with yyyy-mm-dd. That one always works.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com | | | | re: Date Format and using database in various countries
I have many customers in Australia and the UK, but the bulk in the US. After
numerous enquiries from Australian users with bizzare behavior using
dd/mm/yyyy, I did end up adding a date format option for either dd-mmm-yyyy
or mmm-dd-yyyy, and that, as you say, eliminates all doubt regarding the
date.
"SmartbizAustralia" <tom@smartbiz.com.auwrote in message
news:1189944570.992327.107560@57g2000hsv.googlegro ups.com... Quote:
Dates are a serious issue in Access especially down under in
Australia!
>
We use the british system and cannot figure out why access uses the mm
\dd\yyyy format sometimes.
>
The way we always do vba is to use MMM where applicable.
>
That way there is no doubt as to the date...
>
This works against sql server as well so it is more universal than the
function to switch at date to the american format above.
No offense meant to Allen but just use format to make your month three
characters and life is easier!
>
e.g. format(MyDate(),"dd-mmm-yyyy") works with no issues against any
system....for australian/UK users and even using format(mydate(),"mmm-
dd-yyyy") for us systems works fine..
>
Note: the format - yyyy-mm-dd is one of the best for queries against
other databases - e.g. dbII or sql server......and most systems
shouldn't have an issue with this.....
>
Regards,
Tom Bizannes
Sydney, Australia
>
| | | | re: Date Format and using database in various countries
ARC wrote: Quote:
I have many customers in Australia and the UK, but the bulk in the US. After
numerous enquiries from Australian users with bizzare behavior using
dd/mm/yyyy, I did end up adding a date format option for either dd-mmm-yyyy
or mmm-dd-yyyy, and that, as you say, eliminates all doubt regarding the
date.
>
>
"SmartbizAustralia" <tom@smartbiz.com.auwrote in message
news:1189944570.992327.107560@57g2000hsv.googlegro ups.com... Quote:
>Dates are a serious issue in Access especially down under in
>Australia!
>>
>We use the british system and cannot figure out why access uses the mm
>\dd\yyyy format sometimes.
>>
>The way we always do vba is to use MMM where applicable.
>>
>That way there is no doubt as to the date...
>>
>This works against sql server as well so it is more universal than the
>function to switch at date to the american format above.
>No offense meant to Allen but just use format to make your month three
>characters and life is easier!
>>
>e.g. format(MyDate(),"dd-mmm-yyyy") works with no issues against any
>system....for australian/UK users and even using format(mydate(),"mmm-
>dd-yyyy") for us systems works fine..
>>
>Note: the format - yyyy-mm-dd is one of the best for queries against
>other databases - e.g. dbII or sql server......and most systems
>shouldn't have an issue with this.....
>>
>Regards,
>Tom Bizannes
>Sydney, Australia
I would suggest that you carefully reread the replies of Allen Browne
and Rick Brandt, and also the article Allen Browne linked to.
When doing dynamic SQL, mmm-dd-yyyy or dd-mmmm-yyyy are *not* safe
formats.
For instance, they will barf with the settings I use. In particular for
December (des) and October (okt).
Both the format given by Allen Browne mm\/dd\/yyyy and the format given
by Rick Brandt yyyy-mm-dd are safe (the latter is also called ISO 8601).
If your "bizarre behaviour" relates to the interface, the easiest is
what is also already suggested, don't use any format at all for bound
controls. That way, the operators will get the format of their
regionale.
--
Roy-Vidar |  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
