How to over-ride US Date Format?

[Guest]

Sorry to post again so soon but I'm now battling with trying to get a date
into UK format (dd/mm/yyyy).
Below is the code for the GetStartDate function that is called in a number
of queries as 'Between GetStartDate(x) And GetEndDate(x):

Public datStart As Date
Public datEnd As Date
Public x As Integer
Public y As Integer

Public Function GetStartDate(x As Integer) As Date 'the beginning date used
in most of the queries

If x = 1 Then
datStart = #1/4/2004# 'summer 04
ElseIf x = 2 Then
datStart = #1/1/2005# 'january 05
ElseIf x = 9 Then
x = y
If x = 1 Then
datStart = #1/4/2004# 'summer 04
ElseIf x = 2 Then
datStart = #1/1/2005# 'january 05
End If
End If

GetStartDate = Format(datStart, "dd/mm/yyyy")

End Function

The GetEndDate function is obviously very similar.
I've tried using FormatDateTime in the function -eg, 'datStart =
FormatDateTime("01/04/2004", vbShortDate) and it's still returning records
from 4th January instead of 1st April. How can I get this to change guys?
I've checked my Regional Settings and they're definitely in UK format.
I do hope you can help me out!
Kind regards,

Lee

 

[Marshall Barton]

Sorry to post again so soon but I'm now battling with trying to get a date
into UK format (dd/mm/yyyy).
Below is the code for the GetStartDate function that is called in a number
of queries as 'Between GetStartDate(x) And GetEndDate(x):

Public datStart As Date
Public datEnd As Date
Public x As Integer
Public y As Integer

Public Function GetStartDate(x As Integer) As Date 'the beginning date used
in most of the queries

If x = 1 Then
datStart = #1/4/2004# 'summer 04
ElseIf x = 2 Then
datStart = #1/1/2005# 'january 05
ElseIf x = 9 Then
x = y
If x = 1 Then
datStart = #1/4/2004# 'summer 04
ElseIf x = 2 Then
datStart = #1/1/2005# 'january 05
End If
End If

GetStartDate = Format(datStart, "dd/mm/yyyy")

End Function

The GetEndDate function is obviously very similar.
I've tried using FormatDateTime in the function -eg, 'datStart =
FormatDateTime("01/04/2004", vbShortDate) and it's still returning records
from 4th January instead of 1st April. How can I get this to change guys?
I've checked my Regional Settings and they're definitely in UK format.

I don't have to use non-USA dates, so take this information
with care.

Rule 1 about dates: All dates inclosed in # signs must be
in an unambiguous format (e.g. yyyy-mm-dd) or they will be
interpreted as USA format. Your code above violates this
when you wrote #1/4/05#, since it is ambiguous, it must be a
USA date (4 Jan 2005).

Rule 2: You can get your international locale setting to be
used by letting Access convert a date in a string either
automatically:
datevariable = "1/4/05"
or explicitly:
variantvariable = CDate("1/4/05")
both of which will result in 1 April 2005.

But, I would feel very uncomfortable with the implicit
conversions in your code. For either of those latter two
approaches, you can not rely on what date you are specifying
since moving your program to a different machine with
different settings would convert the date strings
differently. Therefore, I STRONGLY advise you to use the #
syntax with an unambiguous format.

Note that I am not sure what happens when you use somethng
like:
#1 April 2005#
with a non English language setting.

Rule 3: None of this matters to your users when entering
data to a date field/control, since the date will be
converted automatically using their own locale settings
(unless they do something unusual to force the issue).

 

[Guest]

Thank you so much for the comprehensive reply. That's really helpful.
Kind regards,

Lee