How to make the code work in both US and UK date formats?

R

Ray2216

Guest

• Nov 30, 2004

• #1

In a database that used to be run solely on computers with US date format, there are pieces of code like this:-

Code:

" WHERE [DateField] Between #" & Me.txtStartDate _
       & "# And #" & Me.txtEndDate & "#;"

It works fine on US dates. Now we have established some offices in regions that use UK date format. In these regions, the code sometimes works but, at other times, doesn't.

How should I modify the code so that it will work in both US and UK date formats?

Any help is greatly appreciated.

 

J

Jon K

Registered User.

Local time

Today, 15:12

Joined

May 22, 2002

Messages

2,209

• Nov 30, 2004

• #2

You can do it in two ways.


Method 1

Use the Format() function to convert the text box values to US date format before delimiting them with the # sign:-

Code:

" WHERE [DateField] Between #" & Format(Me.txtStartDate, "m/d/yyyy") _
       & "# And #" & Format(Me.txtEndDate, "m/d/yyyy") & "#;"

Method 2

Use the DateValue() function on the text box values, as if they were text:-

Code:

" WHERE [DateField] Between DateValue('" & Me.txtStartDate _
       & "') And DateValue('" & Me.txtEndDate & "');"

Both methods work on US and UK systems (probably on other systems, too.)
.

 

Last edited: Nov 30, 2004

F

filo65

Registered User.

Local time

Today, 16:12

Joined

Oct 22, 2004

Messages

38

• Nov 30, 2004

• #3

you could also use CLng

" WHERE [DateField] Between " & CLng(Me.txtStartDate) _
& " And " & CLng(Me.txtEndDate) & ";"


I use it for German - US Systemd

 

R

Ray2216

Guest

• Dec 1, 2004

• #4

Thanks guys.

Jon K's two methods worked flawlessly.

filo65's method of using CLng at first kept throwing a Run-time error '13' Type mismatch until I had to format the two unbound text boxes as Short Date in the property sheet.


Now all three methods work. I wonder would there be a fourth one!

 

J

Jon K

Registered User.

Local time

Today, 15:12

Joined

May 22, 2002

Messages

2,209

• Dec 1, 2004

• #5

Here's a fourth one.

" WHERE Format([DateField], 'yyyymmdd') Between " & Format(Me.txtStartDate, "yyyymmdd") _
& " And " & Format(Me.txtEndDate, "yyyymmdd") & ";"

Don't ask for a fifth one. You have had more than enough.
.