Query criteria current date, minus 1, between time (1 Viewer)

B

bruceblack

Registered User.

Local time

Today, 03:27

Joined

Jun 30, 2017

Messages

119

• May 21, 2018

• #1

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

Code:Copy to clipboard

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

 

Ranman256

Well-known member

Local time

Yesterday, 22:27

Joined

Apr 9, 2015

Messages

4,339

• May 21, 2018

• #2

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])

 

B

bruceblack

Registered User.

Local time

Today, 03:27

Joined

Jun 30, 2017

Messages

119

• May 22, 2018

• #3

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

bit cruncher

Local time

Yesterday, 19:27

Joined

Mar 17, 2004

Messages

8,180

• May 22, 2018

• #4

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...

Code:Copy to clipboard

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