Check for Time in Range

[oihjk]

I have an apparently unique challenge that I thought would be pretty common. I have a payroll system that tracks payroll per job. The entries are on a time range basis. So on job 2, if an employee (0520) works from 9:00 to 9:15 and then goes to job 3 from 9:15 to 9:30 then returns to work on job 2 from 9:30 to 10:30 the entry will look something like this (somewhat scaled down for illustration purposes):

Employee# | Job# | StartTime | EndTime
0520------|2-----|9:00-------|9:15
0520------|3-----|9:15-------|9:30
0520------|2-----|9:30-------|10:30

If a timekeeper comes back and enters 10:00 to 10:15 (or even 10:00 to 11:00, basically any portion of a time range that has already been used for that employee), I need to be able to recognize that this falls within the time range and warn the user there is an error in the input.

I understand how to check for one of the records, but to go through every record to make sure there are no conflicts is not presenting itself to me. Can somebody at least point me in the right direction? I am sure I can get this going, I just need a helping hand.

Thanks for any help in advance...

 

[ajetrumpet]

Employee# | Job# | StartTime | EndTime
0520------|2-----|9:00-------|9:15
0520------|3-----|9:15-------|9:30
0520------|2-----|9:30-------|10:30

If a timekeeper comes back and enters 10:00 to 10:15 (or even 10:00 to 11:00, basically any portion of a time range that has already been used for that employee), I need to be able to recognize that this falls within the time range and warn the user there is an error in the input.

I understand how to check for one of the records

How do you check for one of the records?? Have you written a bit of code for this??

If you explain what you're doing to check for one of the records, maybe we can give you a boost on how to extend that effort.

 

[pbaldy]

Not that tricky. Generally speaking, check for start times in the table < the proposed end time from your form and with end times in the table > the start time on the form. I assume you'd need to further add date and employee to the criteria. I'd open a recordset on a query that counted conflicting records and test for that returning > 0.