Using Between Dates with General Date Format - '97

[EllieFant]

Good Afternoon,

I have an Audit table that stores the date and time that a record was changed using the General Date Format (in the table). I am creating a report that will show all the records changed between dates (the user chooses on a form with unbound fields for entering the date). In my query I am using the following criteria:

Between [Forms]![frmChangeReports]![txtStartDate] And [Forms]![frmChangeReports]![txtEndDate]

Problem is that if a record was changed on 8/23/2004 9:17:05 AM and on my search form I put 08/01/04 to 08/23/04 I don't get the record referenced above.

I also tried:

Between [Forms]![frmChangeReports]![txtStartDate] And [Forms]![frmChangeReports]![txtEndDate]+1

and get told the caclulation is too complex.

Is there an easy solution to this (other than changing the format of the date in the table)?

Let me know if you need more information.



Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

elliefant@qwest.net

 

[PHV]

Have you tried something like this ?
WHERE Format([Name of DateTime field],'yyyymmdd') Between Format([Forms]![frmChangeReports]![txtStartDate],'yyyymmdd') And Format([Forms]![frmChangeReports]![txtEndDate],'yyyymmdd')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

DateValue() is another option - it returns the date without the time.

DateValue(YourDate) Between [Forms]![frmChangeReports]![txtStartDate] And [Forms]![frmChangeReports]![txtEndDate]