Time Calculationon Form Field

[Pascal E]

Hi everone - new to ths forum here and was hoping somone can help me with the following problem. I searched the forum for a solution and there seems to be a few threads on it but I still can't get it to work.
Running Access 2007, split database with the backend on a shared server.
I have the below formula in a form to calculate the total hours from 3 fields. The fields are formated as numbers and are entered like 10.40 and 10.30 and 10.30 as an example. These 3 times should come to a total of 31.40 eg. 31 hours and 40 minutes. The below formula results in 31.00 showing me that the formula calculates the minutes still to 100. This is driving me truly mad any help would be greatly appreciated!

=Int([hours1]+[hours2]+[hours3])+Format(([hours1]+[hours2]+[hours3])-Format(Int([hours1]+[hours2]+[hours3])) Mod 60)

 

[datAdrenaline]

First note ... the Format a value is displayed as, has nothing to do with how the value is stored.

What are the data types of Hours1,2,3 ... if the datatype is Number/Double or Number/Single, then you will not get your times to work right with out a little coersion, simply because number datatypes are based on the decimal system, and with the way your are trying to do this you are using the left of the dec as a base 10 number and the right as a base 60 number. With that in mind, I think I would approach this with a User Defined Function.

Do take note, however, that using the format you have chosen (hh.nn), a user can definately get into trouble, so I make my post with the hopes you will change some stuff around. I make this claim of "trouble" simply because a user can easily type in something like 10.3 in the hopes of meaning 10hrs and 3minutes, however, it will be interpreted as 10hrs and 30minutes. If the user wants 10h 3m, then the value of 10.03 must be entered.... ok ... on with the UDF ...

Public Function SumHours(ParamArray vHours()) As String
 
    Dim x As Integer
    Dim curHours As Currency
 
    If IsArray(vHours) Then
 
        'Sum up the hours (result will be in decimal hours)
        For x = LBound(vHours) To UBound(vHours)
            curHours = curHours + Int(vHours(x)) + (vHours(x) - Int(vHours(x))) * 10 / 6
        Next x
 
        'Now convert back to a base 60 for the right side of the decimal, and return result
        SumHours = Format(Int(curHours) + ((curHours - Int(curHours)) * 6 / 10), "#.00")
 
    End If
 
End Function

The code should be placed in a Standard Module.

Then in the control's Control Source property, you can use the expression:

=SumHours(Nz([Hours1],0), Nz([Hours2],0), Nz([Hours3],0))

......

Also, just a point of note ... with out knowing your situation, it appears as though your table is not normalized. I would recommend that you investigate normalization guidlines and adjust your table design accordingly.

 

[Pascal E]

Hi datAdrenaline,
Works perfectly - thanks for your help mate!