Query criteria current date, minus 1, between time

[bruceblack]

Hi guys! I was wondering if its possible to have the following query criteria:

I have a datefield and a timefield. I would to include

- Todays date
- Also the date minus 1 day
- Where the minus 1 day only between 00:00 and 03:00 Am

This has to do that we work till 3 am every day and the query needs to include that.

I tried this but its not working

=Date() AND  DateAdd ("dd", -1, Date() Where [starttime] > TimeValue("00:00:00") And Time < TimeValue("03:00:00")

 

[Ranman256]

You don't need 2 fields for date and time. 1 field holds both and it's better to calculate range.

But date math is done with DateAdd() and DateDiff()
Your query would be:
Between [dateFld] and DateAdd("d",-1,[dateFld])

 

[bruceblack]

Thanks for the swift reply! Appreciated.

However, my main problem was including the in between set time also in the same criteria (see first post)

Still stuck here

 

[MarkK]

You essentially have a fixed three hour offset in your data, right? So to look for equality with a date, subtract those three hours from your stored data, take the DateValue() of that result, and compare that to a date, and since the data in your table is stored in two fields, just add those together first, like...

WHERE Date() - 1 = DateValue(DateInTable + TimeInTable - #03:00:00#)

Obviously that WHERE clause is not a turn-key solution, but it demonstrates the idea.
hth
Mark