Query Date Question (1 Viewer)

[kjbrack]

I am wanting to run a query that shows all the records between two different dates but between hours. A work day starts at 7:30am and ends at 3:00am the next day. I have a form that I enter the date into that feeds the query. The table field name is day and it is in the date/time format. Here is what I have in the query:

Between [Forms]![By Date Production Cell Total Form]![Text18] And [Forms]![By Date Production Cell Total Form]![DateSelector]

Text18 is the date I put in
DateSelector is Text18+1

This gives me all the records over a complete date, thus we are getting the end of the night shift added from the pervious day and we are missing the end of this working day night shift.

Any help would be great.

 

[Minty]

The date selector will only put in a time and date with midnight as the default time. e.g. 30/08/2016 00:00

You would have to add / subtract the time part you want to your date values using the DateAdd() function

 

[kjbrack]

Can you give and example using between and DateAdd together.

 

[Minty]

Okay based on

A work day starts at 7:30am and ends at 3:00am the next day.

Try This

Between DateAdd("n",[COLOR="Red"]480[/COLOR],[Forms]![By Date Production Cell Total Form]![Text18]) And DateAdd("n",[COLOR="red"]180[/COLOR], [Forms]![By Date Production Cell Total Form]![DateSelector])

The 480 and the 180 should move the time from midnight to 7:30am and 3:00am.
The full list of syntax for DateAdd is here; https://www.techonthenet.com/access/functions/date/dateadd.php

 

[kjbrack]

Thank you, that worked!!!!