I need a query that defaults to current day with between time stamps

[trythis]

I want a query to return current date with starttime stamp between 1am and 4pm

Then I plan to create another query that will return current date with starttime stamp between 2pm and 10pm

I have departments that have day and and night shift so I have shiftstart date/time and shiftend date/time.

as well as different departments that need to show all on one page.

I thought I would create two queries and show them side by side. day shift on left night shift on right.

I found this code to put in my select query but it did not work. I do not know what I need to change to make this work but this is what I need.

BETWEEN DateAdd("h",-1,Date())+TimeSerial(0,0,0)) AND
DateAdd("h",1,Date()+TimeSerial(0,0,0)

My opjective is to run a report that shows

8/24/2009 current date
Day shift query.....Night shift query

All on one page like a spreadsheet.
so If I create two reports tied to two queries that give me the parameters above I should have a spreadsheet looking report?

Any better suggestions would be great.

More examples would be

8/24/2009 current date
Day shift query.....Night shift query
Dept1 Day Shift.....Dept1 Night shift
Dept2 Day Shift.....Dept2 Night shift
ETc....

I do not know how to make it show the data side by side without creating different queries and reports.

 

[namliam]

BETWEEN DateAdd("h",-1,Date())+TimeSerial(0,0,0)) AND DateAdd("h",1,Date()+TimeSerial(0,0,0))


Why the timeserial here? It does nothing... your basicaly saying lets assume date = 24 aug 2009
Between 23-aug-2009 23:00 and 24-aug-2009 01:00

Respectively "dateadd-ing" -1 and +1 hour ("h") to either date... From your description I dont think this is what you are looking for, but now with this knowledge perhaps you can solve your own riddle?

Good luck!

 

[trythis]

BETWEEN DateAdd("h",-1,Date())+TimeSerial(0,0,0)) AND
DateAdd("h",1,Date()+TimeSerial(0,0,0))

I just found the above code and the person who wrote it said,

"That should return all records where the time stamp is between 11PM on the previous day and 1AM on the current day."

Not what I need but I could not figure out how to tweek it to get what I want but I thought I was on the right track??

Thanks,
Tina

 

[namliam]

Not what I need but I could not figure out how to tweek it to get what I want but I thought I was on the right track??

Well, tina, your on the right track...

Here is a neat little trick: F1

I.e.
Go to VBA (make a new module), type in your above code now subsequently:
Select TimeSerial, press F1, read all
Select DateAdd, press F1, read all

Let me know what you learn and what YOU think is the right solution?

Good luck & Happy reading!