Is Access 2003 overiding Regional date Settings?

[Peter-ERHT]

Can anyone help before I pull out what’s left of my dwindling hair?

I am having trouble with UK dates (dd/mm/yyyy) in Access 2003. My Regional
Settings are set to UK but Access seems to be ignoring this and insisting on
using US dates.

I have a form with some unbound text boxes (which are formatted to Medium
date) in which I wish to enter default dates which users can then change if
need be. (These dates are picked-up later by queries). As these dates are
different depending on which option the user has chosen earlier in the form,
I am setting the dates programmatically:

Private Sub CmdNov_Click()
'set default dates for selected period
Me.TxtStartersFromDate.Value = #1/9/2009#
Me.TxtStartersToDate.Value = #11/30/2009#
End Sub

(In this instance the required dates are 1st Sept 2009 and 30th Nov 2009.)

The dates returned from the example above in the form show as 09-Jan-09 and
30-Nov-09

Two things seem to be happening here:

1. Access is interpreting Me.TxtStartersFromDate.Value = #1/9/2009# as
09-Jan-09

2. When I enter the date #30/11/2009# in the Visual Basic Editor, as soon
as I move the cursor from that line the entry is automatically changed to
#11/30/2009#.

What the heck is going on?


As an experiment, I entered the dates directly into the Default properties
of the text boxes on the form.

Both the dates 01/09/09 (meaning 1st Sept 2009) and 30/11/2009 (meaning 30th
Nov 2009) returns 30-DEC-1899 in the boxes.

What the double heck is going on?


All the posts I have read say that Access follows the Regional Settings, or
have lots of heavy techni-speak which is well beyond me. I have changed my
Regional Settings to US and re-booted my PC, then changed them back to UK and
re-booted again, just in case UK settings weren’t installed correctly, but
the problems persist.

All I want to do is put some UK dates in text boxes!
Can anyone shed some light on this?


Many thanks,

 

[Jeanette Cunningham]

Hi Peter,
yes, access does expect dates to be in US format.
When I want to set default dates in Australia, I do it like this-->

Me.StocktakeDate.DefaultValue = SQLDate(TheDate)


Replace TheDate with your date.
SQLDate is a function that converts Australian format date to US format.

Here is the function-->
-----------------------
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
On Error GoTo Err_Handler
pstrProc = "SQLDate"

If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler

End Function

 

[Jeanette Cunningham]

Sorry, forgot to take my error handler specific code out of the function so
that it will work for you.

Here is the amended code-->
----------------
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
On Error GoTo Err_Handler

If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
msgbox Err.Number & ", " & Err.Description
Resume Exit_Handler

End Function

 

[Ron2006]

In general it is probably safe to say:

My Regional Settings controls how the date is displayed on forms etc.
Any internal programming usage of dates must be in US format.

Ron

 

[Douglas J. Steele]

In general it is probably safe to say:

My Regional Settings controls how the date is displayed on forms etc.
Any internal programming usage of dates must be in US format.

Or any unambiguous format such as dd mmm yyyy or yyyy-mm-dd