I found some code that I thought would work for me, but when I removed the Date() as a Default Value for StartDate, the code keeps beeping that StartDate must be before EndDate. I don't see the problem. I attached the db hoping someone esle can spot the problem.
I suspect that the code is adding and subtracting dates not the elapsed time. To get the elapsed time, look up "datediff" in Access help. Also, in Access2007 there is a help entry titled: "How to Calculate Elapsed Time". I have not attempted to test your code out.
You need a default value in the start date. When you enter a new record the current date date is displayed in the Start Date AND the default value of the End Date is set to the Start Date. When you Exit from the Start Date the function DatesAreBad is called and you receive a message as you have no valid dates to compare.
If you do not want to use a default date for Start Date you must also remove the default value for End Date, then check that a date is entered after the user moves from the field.
You need a default value in the start date. When you enter a new record the current date date is displayed in the Start Date AND the default value of the End Date is set to the Start Date. When you Exit from the Start Date the function DatesAreBad is called and you receive a message as you have no valid dates to compare.
If you do not want to use a default date for Start Date you must also remove the default value for End Date, then check that a date is entered after the user moves from the field.
Okay, it worked. But I wanted the EndDate to equal the StartDate because most of the time the work is in the same day unless it is shift work--that is, start at 5 pm and end at 1 am.
I will start off completely avoiding your issue --- I will get back to it, but I would like to talk to you about dates and how they are stored. Dates are stored as an 8 Byte floating point number (same spec as Number/Double) and as such you are ALWAYS storing a Date component and a Time component with a field that is typed as Date/Time. So when you format a control to display (or accept) only the date portion, you are actually storing the date with a time component of 12:00 AM. The same type of thing is true when you set up a control to display (or accept) only the time portion, you will get a date component of 12/30/1899 (US format). You see, If you were to look at the raw value stored, for example: right now, for me, it is 7/10/2010 11:20:02 PM (US Format), which is stored as the numeric value (aka: Date Serial number) of 40369.9722453704. The 40369 represents the number of days away from a base date of 12/30/1899 12:00 AM (a date serial of 0.0). The .9722... represents the portion of a day that has elapsed since midnight. So ... if you resolve things, it works out like this:
DateAdd("d", 40369, #1899-12-30#) returns a date of 7/10/2010 (US format)
And ...
DateAdd("s", .9722453704 * 24 * 60 * 60, #12:00 AM#) returns a time of 11:20:02 PM
{here is where the 24, 60, and 60 come from: .9722453704 days * 24 hours/day * 60 minutes/hour * 60 seconds/hour}
It is for this reason that you really SHOULD NOT have 4 date/time fields to represent two moments in time. You should simply have TimeIn and TimeOut and your entry into those fields should be a full date and time.
---
Now on with your issue ... If you keep your current configuration, the code you are using will not function the way you wish because the time component is 12:00 AM for both the Start and End dates, so --- your code will only see a difference in Start and End if your start and end are on different dates, or ... you add your time fields to the start and end fields respectively within the code. Like this:
But ... if you use these expressions, be sure to note that Null values will raise an error. So, you should test for valid dates before proceding with your code. Possible something like this in your event code to prevent a call to your validation code, unless its appropriate to do so ...
Code:
' Verify that dates make sense
If IsDate(Me.txtTimesheetStartDate) And IsDate(Me.TimesheetTimeIn) Then
If DatesAreBad Then
MsgBox "Start date and time must be before end date.", vbCritical
Cancel = True
End If
End If
----
But, I personally would urge you to check your date input using the Forms BeforeUpdate event because you can't enter both (or all four in your case) dates at the same time, so a validate of the dates is not appropriate until all controls are filled in. Thus my encouragement for using the Forms BeforeUpdate event, instead of eash individual control. The BeforeUpdate can be canceled and thus put the user back into the form in order to correct the date discrepancies.
I hope this helps! ... If not, please let us know.
I would change the code for txtTimesheetStartDate_Exit by removing the code for If DatesAreBad and add Me!txtTimesheetStartDate = Me!txtTimesheetEndDate.
Then in the cmdOK_Click change the code to the following, this will do a final final check on the dates before the form closes. You will have to change the coding to include Overnight shifts.
Private Sub cmdOK_Click()
On Error GoTo ErrorHandler
' Verify that dates make sense
If DatesAreBad Then
MsgBox "Start date must be before end date.", vbCritical
Cancel = True
Else
DoCmd.Close acForm, "fdlgTimesheetDetail", acSavePrompt
End If
I would change the code for txtTimesheetStartDate_Exit by removing the code for If DatesAreBad and add Me!txtTimesheetStartDate = Me!txtTimesheetEndDate.
Then in the cmdOK_Click change the code to the following, this will do a final final check on the dates before the form closes. You will have to change the coding to include Overnight shifts.
Private Sub cmdOK_Click()
On Error GoTo ErrorHandler
' Verify that dates make sense
If DatesAreBad Then
MsgBox "Start date must be before end date.", vbCritical
Cancel = True
Else
DoCmd.Close acForm, "fdlgTimesheetDetail", acSavePrompt
End If
It is much better to use acSaveNo than acSavePrompt. Users will not understand that the prompt is for DESIGN CHANGES to the form and NOT to records. There should be no design changes occurring at runtime anyway, and also, if you are using an MDE/ACCDE file it wouldn't be proper to have that show up anyway because they couldn't save form design changes anyway.
So your line should be:
DoCmd.Close acForm, "fdlgTimesheetDetail", acSaveNo
Personally, I would do all the data validation when the user clicks on the OK button. In my databases when a user clicks OK to close the form after entering data I call a function that check the required data validation then returns a value, if the value is not zero then a suitable message is displayed, otherwise the form closes.
Something to think about--don't get it fully. I guess that why you set the Format to Standard or mm/dd/yyyy and so on.
Anyway, I got this code from a book I was reading. It had tips re Validating Data before Saving It. Book title: Automating Microsoft Access with VBA, page 172-173.
I am still basic and learning. I thought I could do something simple and quick.
Like I was saying earlier this code was from a published book. So, you're saying the author didn't take that (overnight shifts) into consideration. If that's case, not a good tip they offered--huh!
Personally, I would do all the data validation when the user clicks on the OK button. In my databases when a user clicks OK to close the form after entering data I call a function that check the required data validation then returns a value, if the value is not zero then a suitable message is displayed, otherwise the form closes.
It is a poorly coded and formatted form. Enter 08:00 start and a End Time of 07:00, change the lunch break to a value say 0.5 or 1 so that it is updated and you will notice a negative hours worked. This means there is no validation performed on the times entered.
There maybe no reason for overnight shifts.
For overnight shifts
1. Another field on the form for the user to select the type of shift Day or Overnight.
2. When the user enters the start date, use code to check if overnight shift then add one to the start date to display the end date.
3. After the user enters a lunch period then check for overnight. If Overnight then add 24 to the hours worked. If you enter 5pm to 1am with a half hour lunch break the total hours will be -16.5hrs. Adding 24 to this total will make the total 7.5hrs which is the correct value.
Let me know what your requirements are for overnight shifts and also the maximum hours per shift and I will give you a sample database.
Edit
I would not display the Navigation buttons at the buttons, but would have buttons for the user to Add another record and Close.
... My friend (Scott Diamond) and I wrote the Access 2007 edition to that book ... Scott was the lead, as I just wrote 4 chapters on ADO and DAO. But, I have to say that I thought the orginal that you referenced (if the link I provided is the correct book) ... well ... was no such a good book. I found many things that were just incorrect or not very well thought out. It has been a while now since I have read it, and I don't mean any disrespect to the authors as they are both intelligent folks ... but ... well ... I will leave it at that, and recommend the following books (in addition to mine of course )...
In addition, and overall book an Access that I often recommend is ... Access Inside / Out {The link goes to A2010, but they have the Inside/Outs for previous versions as well}
I believe it is poorly designed. Why enter data in all the fields when you can add code to do the calculations.
Personally, I would write down my requirements then start a new database and use the previous database as an example.
Is this for each employee to use or for one person to record employee's attendance etc.?
Depending on your level of knowledge you could have a table with your shift times and lunch breaks and the form could populate the form. As an example on a form you have type of shift combo box that listed the shifts, you enter the Start Date and select the type of shift then code could be used to populate the fields and add 1 day to the End Date for overnight shift.
If the user changed a field then validation would be done before adding a new entry or closing the form etc.
...
