Erroneous time calculations in form

[jethro11]

Hi,
I have designed a form to calculate total downtime in a shift. My form has 4 separate text boxes to hold Start_Date, Start_Time,Stop_Date and Stop_Time. The downtime is calculated by subtracting Stop_Date and Stop_Time from Start_Date and Start_Time and there is a text box assigned for it called txtTotalTime. Here is the code I came up with. The date is entered in mm/dd/yyyy format and the time is entered in military format(13:00, 14:00...) .The value comes up wrong and I frequently get negative numbers. What is the problem? Thanks in advance for any help!


Private Sub Start_Time_BeforeUpdate(Cancel As Integer)

Dim StartDate As String
Dim EndDate As String
Dim dStartDate As Date
Dim dEndDate As Date


StartDate = Format(Start_Date, "mm/dd/yyyy&quot & " " & Format(Start_Time, "hh:mm AMPM&quot
dStartDate = CDate(StartDate)
EndDate = Format(Stop_Date, "mm/dd/yyyy&quot & " " & Format(Stop_Time, "hh:mm AMPM&quot
dEndDate = CDate(EndDate)

txtTotalTime.Value = DateDiff("n", dStartDate, dEndDate)
End Sub

 

[MichaelRed]

Baasically, I'm not sure what you are attempting to accomplish. As 'stated', the process would, at best, return a static value. You will (should) get error from entering the values in the order listed, as the entry / update of start time would occur witn no value in the end dtae and time.

There is LITTLE reason to have seperate date and time fields, as a single one would hold the entire value:

? #12/7/2002 16:30#
12/7/02 4:30:00 PM

? Cdate("12/7/2002 16:30&quot
12/7/02 4:30:00 PM


What is the point of the exercise?

The result is shown simply as a number (Number of minutes between start and end). Even with a timer value, the calculation doesn't use the current date / time value, so there is nothing here but poorly implemented and displayed static calculation of user information.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jethro11]

Michael,
Thanks for your reply, it makes sense. However I'm making modifications to an existing form designed by somebody else and the separate fields for date and time already exist. There are entries in the database for almost 2 full years and making changes will require a lot of work. Is there a simpler way to calculate the time difference in minutes with the existing form design? Thanks!

 

[MichaelRed]

Hmmmmmmmmmmmm,

to avoid the problem, you will need to make SOME changes, so it is a mater of what you are willing to do.

The basic issue of date-time math is not really 'math' at all, but just formatting. The date-time data type is ~~ a double with date part as the whole number part of a day since 'day 0' (Dec. 30, 1899) and time as the fractional part of a day. The various date functions just manipulate the data to DISPLAY it in the more conventional calendar date and wall-clock time formats.

This has been WELL documented in many places, including htes fora and MS Help, so I will not delve more deeply into it.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places