multiple shift time clock hours

[fly_champ]

I looked for quite a while, and didn't find any similar posts, so here goes:

I am trying to determine the hours someone has worked in a day.
I have a unique entry in an excel db for every clock in/ clock out
there are overlapping shifts, ie: 1st shift is 5am to 3:30pm 2nd shift is 3:00pm to 1:30AM

second shift is considered to have worked all their hours on the day they started working their hours, for payroll purposes.

how do I query the database entries so that the second shift people's hours are captured when only one day is entered as criteria?

time clock db:

TimeIn	                  TimeOut  Completed	TotalTime	UserID	HoursWorkedID
8/9/2004 5:00:00 AM	8/9/2004 12:00:51 PM	-1	420	191	50601	
8/9/2004 4:53:15 AM	8/9/2004 11:28:47 AM	-1	395	14	50602	
8/9/2004 5:00:00 AM	8/9/2004 12:00:37 PM	-1	420	186	50603

accrual query sql:

SELECT HoursWorked.TimeIn, HoursWorked.TimeOut, UserInfo.FirstName, UserInfo.LastName, HoursWorked.TotalTime, UserInfo.UserID, UserInfo.EmployeeNumber, UserInfo.IsActive, [UserInfo].[LastName] & ", " & [userinfo].[FirstName] AS FullName, HoursWorked.Completed, *
FROM HoursWorked RIGHT JOIN UserInfo ON HoursWorked.UserID = UserInfo.UserID
WHERE (((HoursWorked.TimeOut)>=[Forms]![SwitchBoard]![Text115] And (HoursWorked.TimeOut)<=[Forms]![SwitchBoard]![Text115]+1) AND ((UserInfo.IsActive)=True))
ORDER BY UserInfo.LastName;

-where text115 is a date entered by the user to view the hourly labor distribution and total time stores the amount of time a person was clocked in

this current query does not capture hours accurately all the time.
My thoughts are to somehow feed a start time into the date since a date really implies a range of time so I would like it to work like:
">= #3:00:00 AM# & text115 & and <= #3:00:00 AM# & text115 & +1" or something, but that particular syntax does not work

I would like to use a global variable as the parameter (date) on the query to eliminate having the form open to update the textbox, but I am not sure how that is done.

thank you for your time
Jon

 

[SydneyGeek]

I had a go at this and came up with this solution:
1. Create a calculated field based on TimeIn, which leaves out the time component -- DateSerial(Year([TimeIn]),Month([TimeIn]),Day([TimeIn]) This makes it possible to use a parameter to pull out the start dates.
2. Below this calculated field, create a parameter like -- [Which Day]
3. Still in Design view, go to Query | Parameters. In the dialog, enter your parameter name and the data type (Date/Time).
When you run the query, you get a parameter pop-up. Enter the date and you should have the data you wanted.

Denis