Search code examples
sqlformsms-accesstextboxdatediff

How do I concatenate textboxes to use DateDiff in MS Access 2010

I have textboxes that I want to use to work out the length of time between two time periods.

Start 08 : 00
End 16 : 00

Break 30

I've tried using DateDiff("n",08&":"&00,16&":"&00) - (30*60) but get the #Name error.

I also then need to convert to hours minutes which if the above function worked correctly i'd used (DateDiff("n",08&":"&00,16&":"&00) - (30*60)) / 60 & ":" MOD(DateDiff("n",08&":"&00,16&":"&00) - (30*60)) to get 7:30 to display in a final textbox.

Solution

  • To calculate the number of hours between the two times, minus the break, we could use:

    (StartHH+(StartMM/60)-(EndHH+(EndMM/60)-(BreakMM/60)

((16)+(00/60))-((08)+(00/60))-(30/60) = 7.5

    ....as long as a shift never crosses midnight.

    Or, same result:

    StratHH-EndHH+(StartMM-EndMM-BreakMM)/60

    Alternate Method using TIMESERIAL:

    (TimeSerial(StartHH, StartMM, StartSS) - TimeSerial(EndHH, EndMM, EndSS) - TimeSerial(BreakHH, Break, BreakSS)) * 24

    (TimeSerial(16, 0, 0) - TimeSerial(8, 0, 0) - TimeSerial(0, 30, 0)) * 24 = 7.5

    ...or formatted:

    Format((TimeSerial(16, 0, 0) - TimeSerial(8, 0, 0) - TimeSerial(0, 30, 0)),"HH\hmm\m") = "07h30m"

    Format((TimeSerial(16, 0, 0) - TimeSerial(8, 0, 0) - TimeSerial(0, 30, 0)),"HH:mm") = "07:30"

    Using Data Validation instead of Multiple Textboxes

    As an example of how data entry could be simplified considerably, and user error eliminated completely, here's a form using Data Validation supplemented by a Total Hours box for quick verification. Another good way to ensure accuracy is if scheduled shifts are available, actual times worked could be compared to notify of large variances.

    img

    I won't get into detailed explanation since it wasn't part of the question (and the OP seems determined not to change) but this only took a few minutes to slap together.

    StartTime and EndTime text boxes
    - Format: hh:nn;;"H:mm"
    - Input Mask: 90:00;0;_
    - Validation Text: Please double check your shift time!

    BreakMins text box
    - Format: 0
    - Input Mask: 00

    Form Module

    Option Explicit

Dim breakHr As Single, totalhr As Single

Private Sub BreakMins_Exit(Cancel As Integer)
    UpdateTotal
End Sub

Private Sub endTime_Exit(Cancel As Integer)
    UpdateTotal
End Sub

Private Sub startTime_Exit(Cancel As Integer)
    UpdateTotal
End Sub

Sub UpdateTotal()
    If Nz(startTime, 0) = 0 Or Nz(endTime, 0) = 0 Then Exit Sub
    breakHr = Val(Nz(BreakMins, 0)) / 60
    '[startTime]/[endTime] textboxes hold full datetime for easy calculation
    totalhr = (endTime - startTime) * 24 - breakHr
    total = totalhr
End Sub