Generating Report via Select criteria

[david pearson]

Hi everyone, very new to this whole arena and have been struggling by but am finally thinking I need a hand!

Essentially I have an Opening form, where my users click on a button to open another form to enter data to my database, which includes which shift they are on.

I have other buttons on the Opening form, which prints a report populated by a query, based on the current time it assumes the shift, so therefore it prints all the records entered by that shift.
But I would like it so when a user forgets to print at the end of a shift (likely) or I want historical records, I can enter the date and shift in a pair of combo boxes on the opening form and it would print the appropriate report, otherwise these combo boxes default to the current time and shift.

The issue I'm having is 1) we have a nightshift that straddles midnight, 10pm to 6am usually but not always on special days (another headache!), so when I query the database for records from 2nd of January for example, with my criteria for date and shift in place, It will give me from 12am to 6am on the 2nd and 10pm until midnight on the 2nd, when I really want 10pm of the 2nd onwards to 6am of the 3rd.

So I'm sure I have to write some creative SQL statement, something along the lines of "all records that ="NIGHT" and are after 12:00 of the date picked, and also all records that say ="NIGHT" before 12:00 the next day as well"

Thats it in words, but getting a syntax correct statement in SQL is driving me wild!

Anyway I do hope what I'm trying to convey is clear, i'm somewhat frazzled at the moment so if it's not please do ask for clarification.

Absolutely any help that pushes me in the right direction would be greatly appreciated.

David

[NeoPa]

I would suggest your solution is far simpler David. Use DateTime values instead of separate date and time ones. The shift starts at a particular time and on a particular date. #7/25/2011 22:30# is earlier than #7/26/2011 2:00# ;-)

[nico5038]

Hmm, looks to me you have 3 shifts and, when the shifts are fixed, I would just use a date and a shift number like:
Date: #2011-07-26#
Shift 1
In a separate table you can record the start and end time of the shift to be displayed on a form/report when needed. For the selection you can use just the date and shift number, thus no "cross midnight" problem will occur.

Idea ?

Nic;o)