Date & Time Formatting to Short(ish) date

[Ice Rhino]

I have an Access 2002 DB that is a friendly interface to a back end SQL 2000 server. This uses 'linked' tables. One of the fields within a table in the SQL table is formatted to dd/mm/yyyy hh:nn:ss.

I wish to write a query that will allow me to select records between two certain dates. The dates that I am slecting are done via Calendar 10. I am using a slightly modified version of an expression that successfully worked in another DB but it does not work here. It returns no data at all. Even if I dump the whole format line and just put into the query #07/10/2003# it does not work. If I put in however #07/10/2003 10:40:36# it does return the one record that has that date and time. I just want to drop the time part of the search parameter

Anybody an Ideas?

Between #" & (Format([Forms]![frm_reports_1]![src_txt_sda], "dd/mm/yyyy")) & "# AND #" & (Format([Forms]![frm_reports_1]![src_txt_fda], "dd/mm/yyyy")) & "#))

Any help would be appreciated

 

[Mile-O]

The DateValue() function.

 

[Ice Rhino]

Thank You, you would care to elaborate a little would you?

 

[Mile-O]

Either of these:

Between DateValue([Forms]![frm_reports_1]![src_txt_sda]) And DateValue([Forms]![frm_reports_1]![src_txt_fda])

Between [Forms]![frm_reports_1]![src_txt_sda] And DateAdd("s", -1, [Forms]![frm_reports_1]![src_txt_fda])

 

[Ice Rhino]

Thank you and I apologise for the cheeky request of elaboration

 

[Ice Rhino]

Mucho Mucho Thanks, that worked great.

Question:- I just used the first example you posted, what does the 2nd line containing dateadd part do that is any different?

 

[Mile-O]

No probs - you don't learn if you don't ask.

 

[Mile-O]

It just takes one second of the time value turning a midnight into 11:59:59 of the previous day.

 

[Ice Rhino]

Ahh OK understood