american date format in VBA

[tom26pr]

Hi there,

When trying to query the DB with the following:
...WHERE ((filed)>=" & UKDate(sFromDat e) & " And (field)<=" & UKDate (sToDate) & ")

I'm getting output in american format.

The UKDate() function seems correct:
Function UKDate(varDate As Variant) As String
If IsDate(varDate) Then
UKDate = "#" & Format$(varDate , "dd\/mm\/yyyy") & "#"
End If
End Function

When debugging this, it shows correct english dates (when I mousover on each), yet it queries and outputs in the american format. The field in question in the DB is set (shows dates) in the english format - I dont understand why it doesn't output the desired english format.
Can somebpdy help?
Thanks in advance!!!

[Stewart Ross]

Hi. I think you'll find that Access is doing its best to make sure the date is in the correct form for SQL interpretation - which as you will see from the HowTo article by NeoPa linked here is the American mm/dd/yyyy form.

If you are comparing date/time values you don't need to format them first - just refer to them directly. If you are referring to text fields on a form you can either format them and pass them as literals (but in mm/dd form) or use CDate to typecast them as dates and compare them without the use of literals.

-Stewart

[tom26pr]

Stewart,

Thanks for the reply.
I'd gone to each query and formated it:
...>=#" & Format(sMyDate, "mm-dd-yyyy") & "# AND...
It then queries the DB using the english date format. I still do not understand it as when using Format(sMyDate, "dd-mm-yyyy") it searches for data using the american way. As if it works opposite... Anyway it works.
Thanks again.

[PianoMan64]

Stewart,

Thanks for the reply.
I'd gone to each query and formated it:
...>=#" & Format(sMyDate, "mm-dd-yyyy") & "# AND...
It then queries the DB using the english date format. I still do not understand it as when using Format(sMyDate, "dd-mm-yyyy") it searches for data using the american way. As if it works opposite... Anyway it works.
Thanks again.

a simplest solution here is simply don't format the date string. MS Access will still treat it as a date simply because when you enclose the value in #, that tells MS Access it is a date. Depending on your international setttings in windows, it will decode accordingly.

example:

[code=vb]
"SELECT * FROM TableName WHERE MyDateField >= #" & Thedatevariable & "#"
[/code]