Query on Date/Time field

[lk5990]

I'm trying to write an automated query to extract records based on a range for a combination date/time field. For example I need all records whose Date/Time field is between 12:00 AM and 11:59 PM on Date() - 30. I've tried writing this with between statements, but I cannot retrieve all the data.

 

[JoeMiller]

You should do this in two parts. Make your query and have one expression that extracts the date of your field, set your criteria to Date()-30. Do the same thing with the time using your Between criteria. Here are the functions to pull out the date and time values;

DateOfMyField: DateValue(MyField)

TimeOfMyField: TimeValue(MyField)

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

but - essientially, the period from 12:00 AM and 11:59 PM is the enntire day? So a single calculated field and criteria will do it.

MyDate: Format([DateField], "Short Date&quot - 30

With Criteria = Date - 30

If you really want to be 'formal',

MyDate: Format(DateAdd("d", -30, [DateField]), "Short Date&quot

using the same criteria
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over