Prevent overlapping times

[Duncan Edment]

My question is, I am sure, a simple one. But nevertheless, one which I
just can't seem to get a grip on.

Staff record their hours of work in an Access database, which helps to
detail what they have been working on, and how much should be billed to
the customer.

I have noticed however, that due to human error or whatever, some times
have been recorded incorrectly. For example:

Start End Description
09:00 11:00 PWHC Project
10:45 11:15 KPMG

Whilst the above times are indeed credible--i.e. an employee could be
working on two things at once, and is waiting for something to
finish--it's just not how we work. We have to end one project, before
starting work on another. So, a correct entry for the above, could be:

Start End Description
09:00 11:00 PWHC Project
11:00 11:15 KPMG

What I want to do is, when a project end time is entered search the
database for any other entries for that employee, on that day. Then,
compare the start time they have just entered, to the end time of the
last entry recorded. If the recently added time is less than the
previous end time, then we have an overlap and a message should be
displayed. If the start time is equal to, or greater than the previous
recorded end time, then all is OK.

I originally had code that performed a query on the database, in the
endtime_AfterUpdate event. This logic seemed to work fine, and the
checking of the time was OK. However, if the person recording time
above has really made a boo-boo, and should have entered the following:

Start End Description
09:00 10:00 PWHC Project
10:00 10:45 KPMG Project Briefing
10:45 11:15 KPMG

The code failed. As it was taking the newly entered start time as
10:00, it was comparing this to 11:15. As it is less than the previous
end time, it was showing an error. However, the times are actually
correct.

So, what's the best place to check the times, and how? Is it in the
AfterUpdate or in a Save? Should I proceed forward through the records
and then display the first incorrect entry?

TIA

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove NO_SPAM.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You really should have time starts on different minutes (or include the
seconds). E.g.:

(minutes) (seconds)
Start End Start End
------ ------ ------ ------
10:35 11:00 10:35:01 11:00:00
11:01 12:00 11:00:01 12:00:00
12:01 12:00:01

That way the query below will work more easily.

You can run a query (in the TextBox's BeforeUpdate event, if you like)
that will check if the entered time is Between any previously entered
times on the same date.

== air code ==
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As Stirng

' Get the user-entered start date & check if OK
strSQL = "SELECT Count(*) FROM table_name WHERE " & _
CDate(Me!StartDate) & " BETWEEN StartTime And EndTime " & _
AND WorkDate = " & CDate(Me!WorkDate)

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs(0) > 0 Then
MsgBox "Overlaps"
End If
== end air code ==

If the entered time is between any previously entered times for the
indicated WorkDate the query will return a number > 0. This will mean
an overlap will occur w/ the entered time.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm6vz4echKqOuFEgEQJZCwCgj1iDtNeIu5IeNJDSqRoCX108138AoIWK
5mbjd1sq0SG/nMGzJDOcp6pf
=xyEB
-----END PGP SIGNATURE-----

 

[Duncan]

MG,

Many thanks for your suggestion, which worked well.

I had previously thought about ensuring that the Start Time was always
1 minute greater than the previous End Time. However, when it came to
calculating project durations and equating this to a working day, our
accountants became aware of the "missing minutes"! If I end working
on something at 11:00 and then start working on something else at
11:01, I have 00:01 unaccounted for. Over the space of a week, this
adds up and becomes a pain to explain to people what it is.
Similarly, coding any calculations to take this minute into account,
and adjusting calculations accordingly proved to be difficult.

As the code stands, it still will not allow me to correct an incorrect
record, in what can only be described as the most sensible way.

Suppose I enter the following:

Start End Description
09:00 11:00 PWHC Project
11:00 11:15 KPMG

and then realise that I should have had a project between these two
times, so that my time looks as follows:

Start End Description
09:00 10:00 PWHC Project
10:00 11:00 KPMG Scope Group
11:00 11:15 KPMG

Changing the 11:00 in the first entry, results in the "Overlap"
message being displayed. If I then add in a new line, indicating the
newly worked times, again I get the "Overlap" message.

I suppose I could go through the database and add 1 second to each and
every Start Time entry, and then code it to add 1 second to each new
Start Time entry. This way, all Start Times will have the additional
benefit of 1 second difference from their comparable End Times.


Unless of course, you or anyone else can think of any other methods
for achieving the same result?

Many thanks & TIA

Duncan

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To avoid the overlap error when you change the 11:00 to 10:00 try this
in the TextBox's BeforeUpdate event (use your control's names):

' Is the new EndTime after the old EndTime value?
If EndTime > EndTime.OldValue Then
' Yes: check for overlap in other records
Else If EndTime < StartTime Then
MsgBox "End Time must be on, or after, the Start Time"
End If

You can do the same thing for the StartTime, just make sure the logic is
for a StartTime not the EndTime.

To be able to use the same minute for one record's EndTime and another
record's StartTime use an evaluation like this:

" WHERE (" & CDate(Me!StartTime) & _
" > StartTime AND " & CDate(Me!StartTime) & " < EndTime) " &_
" AND WorkDate = " & CDate(Me!WorkDate)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAOe4echKqOuFEgEQKrtQCffMj5uuCyzF2cNsMPpwWRftOfT2IAoKTD
Xk3zTUTZmxaMKFM9RB6UXhwd
=r1cb
-----END PGP SIGNATURE-----