Australian dates in access

[MR EDDD]

Hey all,

I have a control box in my form that asks the user to enter a date. It then
takes the date and turns it into the default date so that it will show up on
the next record. It accepts the date and saves it to the table in the
correct format (dd/mm/yyyy) but then when it copies it over to the next form
it converts the date into american format (mm/dd/yyyy). I have worked out it
must be something to do with the code. This is what I currently have in the
relevant section of coding.

Private Sub DateFirstSession_AfterUpdate()
DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#"
End Sub

Any assistance as to how I should code it so that it accepts the correct
date format would be greatly appreciated. I have my regional settings in
Windows correctly set to Australia and am using Access 2003.

 

[Linq Adams via AccessMonster.com]

"There once was a tall man from Perth..."

Sorry! Don't remember the rest of the limerick! But there is, in fact, a tall
gentleman from Perth named Allen Browne, considered by many to be the biggest
Access Guru in the galaxy! He has a small white papar that discusses the use
of non-USA date formats, and might be of interest to you:

http://www.allenbrowne.com/ser-36.html

For this particular problem, you might try formatting the date in the
DefaultValue assignment itself:

Private Sub DateFirstSession_AfterUpdate()
DateFirstSession.DefaultValue = "#" & Format(DateFirstSession.Value,
"dd/mm/yyyy") & "#"
End Sub

or maybe

Private Sub DateFirstSession_AfterUpdate()
DateFirstSession.DefaultValue = Format("#" & DateFirstSession.Value & "#",
"dd/mm/yyyy")
End Sub

Format outputs as string or text, but Access is ususally pretty tolerant in
this regard, i.e. if it looks like a Date, even if it's s string, Access will
accept it as a Date. Strings that look like dates, for example, can be used
accurately in the DateAdd() and DateDiff() functions

 

[KenSheridan via AccessMonster.com]

The DefaultValue property is always a string expression regardless of the
date type, so should be wrapped in quotes characters:

DateFirstSession.DefaultValue = """" & DateFirstSession.Value & """"

The pairs of contiguous quotes within the delimiting quotes characters are
each interpreted as a single literal quotes character. When you use the #
date delimiter in this context it assumes a US or otherwise internationally
unambiguous format, so it takes the date as a string expression in the format
dd/mm/yyyy and, because the US short date format is mm/dd/yyyy, transposes
the months and days provided that the result is a legitimate date.

Ken Sheridan
Stafford, England

 

[Marshall Barton]

I have a control box in my form that asks the user to enter a date. It then
takes the date and turns it into the default date so that it will show up on
the next record. It accepts the date and saves it to the table in the
correct format (dd/mm/yyyy) but then when it copies it over to the next form
it converts the date into american format (mm/dd/yyyy). I have worked out it
must be something to do with the code. This is what I currently have in the
relevant section of coding.

Private Sub DateFirstSession_AfterUpdate()
DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#"
End Sub

Any assistance as to how I should code it so that it accepts the correct
date format would be greatly appreciated. I have my regional settings in
Windows correctly set to Australia and am using Access 2003.

When Access evaluates the expression in the DefaultValue
string, the date in the # signs must be either in USA style
or an unambiguous style (eg. yyyy-mm-dd) I prefer the
latter just because it is unambiguous. Your code should be
more like:
DateFirstSession.DefaultValue=Format(DateFirstSession,"\#yyyy-m-d\#")

Note: If you use / as the separator, then Access will
translate that to the separator specified in your Windows
local settings which may not be a legal separator. In this
case the Format would need to be:
Format(dt,"\#yyyy\/m\/d\#")

Note: The Windows local settings will be used to display a
date whenever you do not specify the Format property in
whatever displays the date. That means that you can not
look at a date in a table/querys/form/report and tell
anything about whether it is "correct" or not.

Note: Access will also use the Windows local settings
whenever it it needs to convert a string that looks like a
date to an actual date value. You can use the CDate
function to do that explicitly or more implicitly when you
specify a date type Format in a text box's Format property,
even if the Format property is different style than the
Windows setting (this is very confusing to me).

The bottom line is your code should always specify a date
string in the unambiguous date style enclosed in # signs.