Passing a UK Date to SQL statement as criteria

[Chris K]

I want to change criteria of a query (just date really) by including the
value from a combo box (Booking = UK Date) and I've noticed after couple
errors that it only likes dates in US format

I tried CDATE() which didn't work and finally used the format function to
change the date to US format

It seems to work fine now but is there better/correct way


With CurrentDb.QueryDefs("Letter to Induction")
.SQL = "SELECT [JHPclients].* From [JHPclients] WHERE [booked] = #"
& Format(Bookings, "mm/dd/yy") & "# ORDER BY JHPclients.[First Name]"
End With

I'm bemused by my use of quotes "" within quotes but the single apostrophes
' didn't work and it still seems to work anyhow?

 

[David W. Fenton]

I want to change criteria of a query (just date really) by
including the value from a combo box (Booking = UK Date) and I've
noticed after couple errors that it only likes dates in US format

When passing a string representation of a date, Jet/ACE SQL expects
a date in US format, yes. If you passed a numeric representation,
you wouldn't have to do that, but that would actually require more
conversion functions than the method you're using.

I tried CDATE() which didn't work and finally used the format
function to change the date to US format

I'm not sure why CDate() wouldn't work. DateValue() doesn't work
because it returns values according to the current date format.
Perhaps that's what's wrong with CDate(), too. The Format() insures
that you're getting a string representation of the date that
conforms to the expected date format (i.e., US date format).

It seems to work fine now but is there better/correct way

With CurrentDb.QueryDefs("Letter to Induction")
.SQL = "SELECT [JHPclients].* From [JHPclients] WHERE
[booked] = #"
& Format(Bookings, "mm/dd/yy") & "# ORDER BY JHPclients.[First
Name]"
End With

I'm bemused by my use of quotes "" within quotes but the single
apostrophes ' didn't work and it still seems to work anyhow?

There aren't any quotes within quotes -- your quotes end and then
you concatenate with an expression that uses quotes.

This string:

"SELECT [JHPclients].* From [JHPclients] WHERE [booked] = #"

....is concatenated with the results of this expression:

Format(Bookings, "mm/dd/yy")

....which is in turn concatenated with this further string:

"# ORDER BY JHPclients.[First Name]"

There are no quotes within the quotes at all.

Also, you seem to imply that "Booking" refers to a combo box. You
should specify its parent, i.e., Me!Booking or Me.Booking.

 

[David W. Fenton]

The date does not have to be in US style, but it must be in
an unambiguous style.

Actually, it has to be in US format *or* in an unambiguous format.