(Solved) – MS Access weird numbers

(solved)-–-ms-access-weird-numbers

I receive weird numbers from a function in VBA.

I have Continuous Forms where is a button from which user can manipulate sum of hours in a text box. This text box is located in Form Footer.

My code goes like this:

Private Sub Option39_Click()
    Dim time As Double

    'calculate time to format
    time = 25 / 24

    If Option39.Value = True Then
         Debug.Print dblTotal
        dblTotal = dblTotal   time
         Debug.Print dblTotal
        Me.txtTotalTeamTotal = FormatUnlimitedHours(dblTotal)
         Debug.Print dblTotal
    Else
        dblTotal = dblTotal - time
        Me.txtTotalTeamTotal = FormatUnlimitedHours(dblTotal)
    End If
End Sub

from debug.print i receive these values

3,66611111111111 
4,70777777777778 
112,986666666667 

which I don’t understand why the dblTotal change its value from 4,70777777777778 to 112,986666666667 Why was the number changed?

FormatUnlimitedHours() function is defined like this:

Public Function FormatUnlimitedHours(time As Variant) As Variant
    'function that can have unlimited number of hours in hh:mm:ss format
    Dim comma As Integer
    Dim hours As Variant
    Dim minutes As Variant
    'switch to hours format
    time = time * 24

    If time > 23 Then

        comma = InStr(time, ",") - 1

        If Not comma < 0 Then
            minutes = "0," & Mid(time, comma   2, Len(time) - comma   1)
            minutes = format(minutes / 24, "hh:mm:ss")
            hours = CDbl(Left(time, comma))   CDbl(Left(minutes, InStr(minutes, ":") - 1))
            FormatUnlimitedHours = hours & ":" & Mid(minutes, InStr(minutes, ":")   1, 5)
            Exit Function
        Else
            'for whole numbers
            FormatUnlimitedHours = time & ":00:00"
            Exit Function
        End If

    End If

    FormatUnlimitedHours = format(time / 24, "hh:mm:ss")

End Function

initial value of dblTotal is defined when the form is loaded

Private Sub Form_Load()

    dblTotal = DSum("sumOfTotalTime", "QueryTime")

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *