Prevent overlapping times being entered

[True.Kilted.Scot]

I have a timesheet database, and I need to prevent people entering
overlapping times.

i.e. If an employee enters the following:

13/04/2006 Joe Bloggs Project X 09:00 12:00

they then cannot enter:

13/04/2006 Joe Bloggs Project Y 10:00 11:00


I found a similar question in this group, and used the code provided in
the BeforeUpdate event, but it doesn't seem to work. My BeforeUpdate
event looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWhere As String
Dim strMsg As String
Dim varID As Variant

strWhere = "([fldDateWorked] = " & Format(Me.[txtDateWorked],
"\#dd\/mm\/yyyy\#") & ") AND " & _
"(([fldStartTime] < " & Format(Me.[txtEndTime],
"\#hh:nn\#") & ") AND " & _
"([fldEndTime] > " & Format(Me.[txtStartTime],
"\#hh:nn\#") & ")) AND " & _
"([fldEmployeeID] = " & Me.[txtEmployeeID] & ")"

If Not Me.NewRecord Then
strWhere = strWhere & " AND ([fldWorkID] <> " & Me.[fldWorkID]
& ")"
End If

varID = DLookup("fldWorkID", "tblTimeSheet", strWhere)
If Not IsNull(varID) Then
strMsg = "Warning" & vbCrLf + vbCrLf + _
"The Date / Times entered clash with" + vbCrLf + _
"an already existing record." + vbCrLf + vbCrLf + _
"Please correct your entries."
If MsgBox(strMsg, vbOKOnly + vbCritical, "!! Entry Clash !!")
Then
Cancel = True
End If
End If

End Sub


In the above, I have used the "fld" prefix to denote a field name in a
table, and the "txt" prefix to denote a field name on a form.

Everytime the event fires, "varID" is null, and so the warning message
never appears, even although I may have entered time that are
incorrect.

Can someone please advise on what I have done wrong?

Rgds

Duncs

 

[Douglas J. Steele]

Regardless of what the Short Date format is on your computer, you must use
mm/dd/yyyy format in SQL statements.*

Change that format statement to "\#mm\/dd\/yyyy\#"

* Okay, this isn't 100% true. You can use any unambiguous format, such as
yyyy-mm-dd or dd mmm yyyy. The point is, Access will ALWAYS try to interpret
the date first as mm/dd/yyyy, regardless of the computer settings. If it
succeeds, it'll use that date. That means that Access will misinterpret
dates for the first 12 days of each month (other than January 1st) if
they're presented in dd/mm/yyyy format.

You might want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[John Smith]

Try:

strWhere = "fldDateWorked = " & Format(Me.txtDateWorked,"\#dd\/mm\/yyyy\#") _
& " AND fldEmployeeID = " & Me.txtEmployeeID _
& " AND ((" & Format(Me.txtStartTime,"\#hh:nn\#" & " < fldEndTime " _
& " AND " & Format(Me.txtEndTime,"\#hh:nn\#") & " > fldStartTime)" _
& " OR (" & Format(Me.txtStartTime,"\#hh:nn\#" & " < fldStartTime" _
& " AND (Format(Me.txtEndTime,"\#hh:nn\#") & " > fldEndTime))" _

The first pair detect overlapping periods, the second pair a period that
completely encompasses an existing record.

Note that this will fail if a session passes midnight, if this is a
possibility then you need to store a complete date and time in the start and
end columns. If you already have dates in the table columns this would also
be a problem and you would need to format the form fields as:
"\#d/mmm/yyyy hh:nn\#"

HTH
John

 

[Albert D.Kallal]

Great answer.

& " AND ((" & Format(Me.txtStartTime,"\#hh:nn\#" & " < fldEndTime " _
& " AND " & Format(Me.txtEndTime,"\#hh:nn\#") & " > fldStartTime)" _
& " OR (" & Format(Me.txtStartTime,"\#hh:nn\#" & " < fldStartTime" _
& " AND (Format(Me.txtEndTime,"\#hh:nn\#") & " > fldEndTime))" _

As a side note...you don't actually need the 2nd set of conditions to test
for a "encompass".....

All you need is the first condition.....

Assuming the request encompasses...we have for example request of

10:00 AM 5 PM

and, we have a reord in the database of

1 PM 2PM

& " AND ((" & Format(Me.txtStartTime,"\#hh:nn\#" & " < fldEndTime " _
& " AND " & Format(Me.txtEndTime,"\#hh:nn\#") & " > fldStartTime)" _

If you plug in the above numbers....you still get a collision....and hence
no need for your 2nd sql...

 

[True.Kilted.Scot]

Thanks for all your help guys.

Got the validation working now.

Cheers!!!!

Duncan