Formatting Dates for Display in Multiple Regions

[J Patrick]

I have a relatively large Access App with lots of forms displaying lots of
dates. Many of the dates contain both date and time elements. This
application is used globally, so the dates need to be able to display
according to the user's Regional Settings. Unless someone else has a better
alternative, the only way I know to accomplish this without have to write
code to change all date formats at runtime is to use standard named formats
(i.e. Short Date, Long Date, General Date, etc.)

PROBLEM: The "General Date" setting for most individuals is longer than the
format that I wish to display in order to conserve space on the form. For
example, instead of displaying "12/21/2006 1:45 PM", I need to display it as
"12/21/06 13:45". Basically, the format I need is a combination of the Short
Date and Short Time formats.

Although I would prefer not to, I've tried having users change their "Date"
and "Time" settings in the Regional Settings to "MM/dd/yy" and "H:mm". When
doing this, Access displays the date part properly and doesn't display the
AM/PM, but it does display the seconds. This defeats the purpose of trying
to save space and makes it difficult to edit dates when the input mask
doesn't contain a space for the seconds.

Any help would be greatly appreciated!

 

[John Nurick]

Perhaps the best approach is to have your app retrieve regional settings
from the registry when it's launched, use these to assemble the
appropriate format string(s), and store them in global variables or a
"settings" table for easy reference during the session. See e.g.
http://www.jsifaq.com/SF/Tips/Tip.aspx?id=0311

 

[J Patrick]

Retrieving the regional settings and storing them is easy. But then where do
you go? All of my attempts tell me that that "format" property of a form
field will accept only the specific formatting characters (mm, dd, ss, etc.).
It will not take references to global variables or functions. So, the only
way I know of to use the settings I retrieved would be to write a function
that loops through all controls on the form (and any subforms) and set the
format at run-time using either the OnLoad or OnOpen events. Since this
database is in some cases open across a WAN, I would really like to avoid
this type of processing... Is there another way to use the stored settings
that I'm missing?

 

[John Nurick]

That's what I had in mind: set the Format properties in the Load event
procedure. I don't see how this could generate significant extra traffic
in the context of the load that ordinary Access operations place on the
network.