Date syntax in SQL string

[jamessl]

Hi,

does anyone know the correct syntax in an SQL string as part of the Recordset Open method to refer to a field on a form.

I currently have -

rstBookingsSchema.Open "SELECT * FROM BookingsSchema WHERE Date = " & Forms!BookingForm!Date & " ORDER BY Period", conn, 1, adLockOptimistic.

This does not return any records even though the Date does exist in the table BookingSchema and on the form.

The form is obviously called BookingForm and the field is Date. I have tried many variations of this and have had no luck. I need to be able to use the Date criteria.

Help!!

 

[Maquis]

try this:

....WHERE Date = #" & Forms!BookingForm!Date & "#....

Just like text fields need to be in quotes, dates need to be in #'s. Maq
<insert witty signature here>

 

[jamessl]

Well, I still can't get any records with the expression typed as you suggested.

If I put a date directly into the expression

eg WHERE Date = #29/07/02# it still returns no records.

If however I enter the date literal in US format

eg WHERE Date = #07/29/02# it does return records.

My problem is, how do I use a reference to a control on a form, or a variable in my Sub, in the WHERE argument so that the SQL expression will recognize the Dates in my Table, which are in 29/07/02 Format. And I have already tried putting the dates in the table in US format, and no luck. I have also tried the DateValue function to try to return the Date in the WHERE clause as a 29/07/02 format date.

mmmmm, syntax, syntax, syntax

 

[RichUK]

WHERE Date = #&quot; & Format(Forms!BookingForm!Date,&quot;mm/dd/yyyy&quot & &quot;#....
Also Date is a reserved word in Access, if that is a field name in your db then you should change it

 

[jamessl]

got it...

don't know if this is the best way, but created an extra hidden field on form and formatted the date as mm/dd/yy.

Then just referred to this field, FormatDate in the SQL string.

seems a bit silly to have to do this, but it works so who cares.

I reckon there should be some way to refer to the dd/mm/yy format date in the SQL string using the DateValue function though, but anyway...

 

[jamessl]

thanks, knew there was a better way - just got your reply after I had sent mine.

 

[WildHare]

Jamessl, you might also want to be aware that dates are NOT formatted when they're stored - as you seem to be thinking re: your statement

so that the SQL expression will recognize the Dates in my Table, which are in 29/07/02 Format.

The dates are in numeric form in the table. They are only FORMATTED when they are retrieved in some manner. And, I believe, Access (if not all of MS) is rather US-centric in its internal handling of date formats, so that no matter what your INTERNATIONAL setting, or how you actually want to see dates formatted, when you search/select/filter, you NEED to use the US format of MM/DD/YY.

I understand this causes no end of frustration and head-scratching among our offshore cousins. Complain to B. Gates, One Microsoft Way, Bellevue WA....

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm