Handling datevalues in SQL
Posted on 2009-05-07
Hi I'm having some trouble getting a recordset to only include records dated > 1yr ago.
Fromdate = DateValue(DateAdd("d", -365, Now))
Todate = DateValue(Now)
Sql = ""
Sql = Sql & " Select [R_PatID] , [R_TestDate] from TEST_RESULTS"
Sql = Sql & " where [R_PatID] = " & DSCohort.Fields("Pat_ID")
Sql = Sql & " and [R_TestDate] between " & DateValue(Fromdate) & " and " & DateValue(Todate)
Sql = Sql & " order by [R_TestDate] asc"
The data is stored in a date time field (dd/mm/yyyy) in an excel 2003 file but I'm getting old dates such as 14/01/2003, 24/10/2004 being returned on Debug.Print DSResultRecords.Fields("R_Testdate") & " " & DSResultRecords.RecordCount
I have tried using :
1/ datevalue [r_testdate] - syntax error
2/ [R_testdate] > ' " & Fromdate & " ' " - returns old values too.
3/ year [R_testdate] > " & year(fromdate - 1) . Works but includes all of last years dates ie 17months worth if I run it in may 09
Is there another way of doing this query to return only records dated from exactly 12 months ago??