Dates converting to US format??

R

RitaMoloney

Registered User.
Local time
Today, 13:48
Joined
May 6, 2004
Messages
50
Hi,

I have a problem filtering date ranges in a subform. On my main form (called frmStore) I have two unbound date fields called txtStart and txtEnd. The user enters Start Order Date and End Order Date and presses return (the dates are entered dd/mm/yy format). These dates’ ranges are then filtered in the subform (called frmsStore). Sometimes this works correctly and sometimes it doesn’t.
When it doesn’t work and I re-enter the dates in US format e.g. mm/dd/yy, it then works.
I have all my date fields in my database set to UK format (dd-mmm-yyyy) but Access must be converting it back to US format.

In the table tblOrders, the OrderDate field is set to Date/Time and the format is set to dd-mmm-yyyy.
In the form, frmStore, the two unbound date fields txtStart and txtEnd the format is set to Short Date.

On Lost Focus event procedure of txtEnd the following code is attached:

Private Sub txtEnd_LostFocus()
Me.frmsStore.Form.FilterOn = True
Me.frmsStore.Form.Filter = "[OrderDate]>=#" & CDate(txtStart) & "# AND [OrderDate]<=#" & CDate(txtEnd) & "#"
End Sub

Any help would be great.
 
I used a function to get around this.

Code: 
Function USdate(EURODate As Date) As String
    Dim month, day, year As Integer

    day = DatePart("d", EURODate)
    month = DatePart("m", EURODate)
    year = DatePart("yyyy", EURODate)
    USdate = month & "/" & day & "/" & year

End Function
 
"[OrderDate]>=#" & Format(txtStart."mm/dd/yyyy") & etc
 
Rich,
I tried your suggestion and am getting the following errors;

"[OrderDate]>=#" & Format(txtStart."dd/mm/yyyy") & etc
I get this error:
Compile error:
Syntax error

"[OrderDate]>=#" & Format(txtStart.[dd/mm/yyyy]) & etc
I get this error:
Run-time error ‘438’
Object doesn’t support this property or method

Hi Johny,
Would you mind explaining a little bit more about the function. Where exactly do I put it and how do I get it to relate to my piece of code?

Thanks for your help.
 
There is a period where there should be a comma in the Format() example Rich gave you.

"[OrderDate]>=#" & Format(txtStart,"dd/mm/yyyy") & etc
 
and it's "mm/dd/yyyy" not "dd/mm/yyyy" plus you have to repeat the Format for the second date I didn't mean etc. literally
 
Hi,
It's working now, great!

Rich, I understood I had to repeat the Format for the second date and you didn’t mean etc. literally. I guess I was being lazy replying by not typing out the full line and just used etc. as well.
Sorry, about changing the date format to "dd/mm/yyyy". I didn’t understand how it would work with "mm/dd/yyyy" until I read the link ChrisO provided to Allen Browne article. Thanks for clearing that up ChrisO. Learned a lot here on how Access handles dates.

Thanks to everyone for all your help.
 
G’day giedrius.

Yes that’s a good method too.

These countries, by default, use the “.” Date Separator.

All German speaking countries
Azeri (Latin)
Finnish
French (Switzerland)
Iceland
Italian (Switzerland)
Norwegian
Russia :D
Swedish (Finland)
Turkish

They will all fail while running their default setting if they are not handled this way.

And it is important to note that any machine anywhere will fail at the hands of the gofingerpoker if they select . as the date separator.
(If dates are not handled correctly in SQL strings. :D )

Regards,
Chris.
 
Last edited:
That's a slight exageration there Bruce :D the problem only occurs when using dates in strings and directly in SQL ;)
 
You must log in or register to reply here.

Similar threads

D
Sorting Date imported as TEXT field in different formats
Replies
9
Views
552
arnelgp
arnelgp
J
Between/And
2
Replies
21
Views
604
Pat Hartman
P
A
FILTER PRINT REPORT
Replies
2
Views
126
arnelgp
arnelgp
N
Solved Converting a longer date string into short date
Replies
8
Views
288
nector
N
S
Solved Copy to Documents\Backups
Replies
8
Views
177
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom