Validating time entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again group,

I have a table (JobCards) with the fields EmployeeNum, JobNum, JobDate,
TimeIn and TimeOut

A user will input both TimeIn and TimeOut...

What I need is a way to validate that the times do not fall within another
range previously entered, based on the EmployeeNum and Date

For example, say I have one record...

Joe01, 5, 5/30/07, 8:00 AM, 9:00 AM

a user starts a new record...

Joe01, 6, 5/30/07, 8:30 AM, 9:30 AM

How can I check to see that the "8:30 AM" kicks out an error message?

Obviously this is the extreme simplified scenario and eventually it would be
checking through the entire days worth of times...

Thanks in advance for the help.
 
Yes, I'm replying to myself...maybe someone else could also use this later...
This is the actual SQL statement...
I have to make sure the employee number and the job date are the same...
then I wanted to make sure the records are different...
then it checks the "TimeIn" OR "TimeOut" so see if it falls between the
"known" range...
if so it shows the record.
I didn't go with the nz() function because I have that controlled elsewhere
so that a null or empty field cannot be entered (no sense checking what's not
going to happen)...
Other that that this is really based off of the SQL from Allen Browne's
website.

Thanks again everyone,
Joe


SELECT JobCards.PrimKey, JobCards.EmployeeNum, JobCards.JobDate,
JobCards.timein, JobCards.timeout, JobCards_1.PrimKey,
JobCards_1.EmployeeNum, JobCards_1.JobDate, JobCards_1.timein,
JobCards_1.timeout
FROM JobCards, JobCards AS JobCards_1
WHERE (([JobCards_1].[EmployeeNum] = [JobCards].[EmployeeNum]) AND
([JobCards_1].[JobDate] = [JobCards].[JobDate]) AND
([JobCards_1].[PrimKey] <> [JobCards].[PrimKey])) AND
((([JobCards_1].[TimeIn] >= [JobCards].[TimeIn]) AND
([JobCards_1].[TimeIn] <= [JobCards].[TimeOut]))
OR
(([JobCards_1].[TimeOut] >= [JobCards].[TimeIn]) AND
([JobCards_1].[TimeOut] <= [JobCards].[TimeOut])));
 
Back
Top