If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Prevent overlapping times
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. |
#2
|
|||
|
|||
-----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/AwUBQm6vz4echKqOuFEgEQJZCwCgj1iDtNeIu5IeNJDSqRoCX1 08138AoIWK 5mbjd1sq0SG/nMGzJDOcp6pf =xyEB -----END PGP SIGNATURE----- Duncan Edment wrote: 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? |
#3
|
|||
|
|||
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 wrote in message ink.net... -----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/AwUBQm6vz4echKqOuFEgEQJZCwCgj1iDtNeIu5IeNJDSqRoCX1 08138AoIWK 5mbjd1sq0SG/nMGzJDOcp6pf =xyEB -----END PGP SIGNATURE----- Duncan Edment wrote: 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? |
#4
|
|||
|
|||
-----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/AwUBQnAOe4echKqOuFEgEQKrtQCffMj5uuCyzF2cNsMPpwWRft OfT2IAoKTD Xk3zTUTZmxaMKFM9RB6UXhwd =r1cb -----END PGP SIGNATURE----- Duncan wrote: 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 wrote in message ink.net... -----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/AwUBQm6vz4echKqOuFEgEQJZCwCgj1iDtNeIu5IeNJDSqRoCX1 08138AoIWK 5mbjd1sq0SG/nMGzJDOcp6pf =xyEB -----END PGP SIGNATURE----- Duncan Edment wrote: 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to view Start-End times in Day & WorkWeek view? | lllusion | Calendar | 2 | January 12th, 2005 01:04 PM |
Outlook express dialog boxes scroll X times before stopping | Bruceybonus | Outlook Express | 4 | January 11th, 2005 06:19 PM |
Trying to count the number of times a "type" of value occurs | astiller | Worksheet Functions | 2 | July 9th, 2004 03:35 PM |
How to get excel to find the numbers of times 2 numbers appear | Gary Hunt | Worksheet Functions | 2 | March 21st, 2004 10:32 PM |