Setting a conditional value in a query to perform an additional qu

[Guest]

Ok, I have a slight brain twister here and wondering if anyone could help
shed some light on this.

Im in the midst of creating a database that will hopefully replace a
paper-based system of storing 'shift runsheets' from a 24/7 office. There is
3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and
Night (10pm to 6:30am). Each day it adds a new row of data to the table
"shift" and users can peform searches on previous sheets by searching the
table "previous" which links to "shift". When a user wishes to search, they
select the shift and the date and it presents them with the results. However,
the issue I have is that when they search for a nightshift, it only presents
the last 2 hours of the date and the earlier 6 hours of the shift from the
night before (not very helpful! ).
Is there a way to set the query to only reveal the relevant information
according to the shift?

 

[Guest]

Try this --
Between DateAdd("h",6+(([Enter shift]-1)*8),[Enter date]) And
DateAdd("h",14.5+(([Enter shift]-1)*8),[Enter date])

It adds 6 hour for start of first shift for the date entered. It takes the
shift number entered and subtracts 1 and then multiplies by 8 and adds 14.5
hours. Your first shift end 14.5 hours into the day.