Time calculation is arriving at different result.
Out-Time....InTime....Hrs
14:00.......17:00.....3.00Hrs(Not 3.12)
This part of the code works as expected.
Private Sub nB01TO_AfterUpdate()
Me.nB01TO = Format(Me.nB01TO, "hh:nn")
Me.TimeB01 = Format(Me.nB01TO, "0.00")
Me.TimeB01.Requery
Me.TimeBTO01 = Format((Me.nB01TL / 6.1), "0.000") / 24
Me.TimeBTO01.Requery
Me.TimeBTO001 = Format((Me.TimeB01 + Me.TimeBTO01), "hh:nn")
Me.TimeBTO001.Requery
Me.nB01EI = Me.TimeBTO001
Me.nB01EI.Requery
End Sub
I break each Time field into its numeric base number so I can compare what they should be against an Excel version of it. The “6.1” is the Pick Rate expected per hour.
[![![Time Out:][1]][1]
The second part of the time: “Actual In” is where the problem lies! The Total Hours are not correct:
[![Actual Time In:][1]][1]
Private Sub nB01AI_AfterUpdate()
Me.nB01AI = Format(Me.nB01AI, "hh:nn")
Me.nBAI01 = Format(Me.nB01AI, "0.000")
If Me.nBAI01 < Me.TimeB01 Then
Me.nB01PH = Format(Me.nBAI01 - Me.TimeB01, "0.00") * 24 + 24
Else
Me.nB01PH = Format(Me.nBAI01 - Me.TimeB01, "0.00") * 24
End If
End Sub
The If statement handles the time difference when the “Actual In” time is after midnight. TIA
3
You are mixing UI handling with business logic (i.e., the calculations). This has many disadvantages:
- This makes it very hard to understand the calculation part.
- It is inaccurate because the controls may return the values as strings, even when they represent dates and numbers. So, you don’t have control over which data types are used for the calculations.
- You may lose precision because of the involved formats.
- It is slow, because the UI is updated several times during the calculation.
Do not store intermediate results in controls. Store them in variables.
-
Get the required values out of the controls and store them in variables having an appropriate type (e.g. Double, Date, etc.). This may require castings (
CDbl,CDate, etc.) -
Do the calculations by using only the variables.
-
Update the Controls with the new values.
If the values are stored in the DB in columns having the right data type (e.g. Numeric Double, Date, etc. instead of Text for numbers and dates), You can use the
Formatproperty of the TextBox to format the values. This is better than using the Format function when assigning the values. It is easier and will also format the values automatically when the user enters values.
Btw., I have a hard time understanding what you are calculating, because I have no idea what the controls with these cryptic names represent. I hope, you will use more descriptive names for the variables.
turns out I didn’t employ enough decimal places, once I expanded the fields to 4, it worked.
I did change some of the formulas to not include formatting and left it for the final step, which also assisted.
Thanks Guys, much appreciated.