Time Overlapping validation

[monkeyman77]

Hello All,

I am attempting to create a database for employee time card entry and I realized i have an issue with overlapping time. I need to find a way to prevent overlapping time per employees. My current database is set up similar to the example attached. If you look at table1 you can see that there are two entry's and one over laps the other.

I am sure there is a way to prevent this, i am just not sure how.

Thank you in advance!
Steven

 

[Ranman256]

run a query to remove the invalid time:
if the next startTime is less than the previous quitTime, then startTime = quitTime
to clip out the overlap.

 

[monkeyman77]

How would I link that query to the form to prevent users from entering the overlapping data?

 

[sneuberg]

You can adapt the idea in this BaldyWeb page to Dcount in a beforeupdate like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "[Table1]", "[Time_In] <= Forms!Form1.Time_Out And [Time_Out] >= Forms!Form1.Time_In And [Employee] = Forms!Form1.Employee And [Date] = Forms!Form1.Date ") > 0 Then
    MsgBox "Error:  Times overlap"
    Me.Time_In.SetFocus
    Cancel = True
End If


End Sub

You can find this implemented in the attached database.

I have a question for you. When you added the field named Date to Table1 didn't you get this warning?

If you got that warning why did you add the field with that name when you could have chosen something like WorkDate?

 

[monkeyman77]

I didn't get that error message when I added the "Date" field.....

Thank you for you quick response!!!

When I attempted to modify my database I ran in to a small error. The original sample i uploaded didn't have the form as a subform and i tried to add this solution to my database and ran in to an error.

Run-time error '2459':
Microsoft Access cannot find the referenced form

I modified the attached sample database to see if that would get the same error and it does.

Any thoughts???

 

[monkeyman77]

Just tried to add "Date" to a blank table and I did get that error. I must have just been rushing to create that sample file. The database I am building doesn't use date.

 

[monkeyman77]

I've figured out how to get the subform to work.

But (there's always a but isn't there......)
I noticed that I am not able to end at a certain time and start at the same time.

Example: i wasn't able to end at 7pm and start at 7pm.

If you look at the attached example I had to start at 7:01pm

 

[sneuberg]

I believe normally intervals are considered to overlap if the contain the same points but I think you can exclude the endpoints by simply changing the <= and >= in the expression to < and >. But I'm not absolutely certain about that so please test that to make sure it works.