Formatting Calculated Fields

[GendoPose]

Hi All,

In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.

I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.

So does anyone know how to format this as a number field properly?

Also for any extra information;
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.

Thanks guys.

 

[pr2-eugin]

- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.

Most definitely not a reason to store the information. TBH the reason begs for Unbound Calculation or Query calculation. IMVHO, storing this information is unnecessary and lot of hassle. Set it to Unbound, set the format at runtime, everything will be okay !

 

[GendoPose]

Most definitely not a reason to store the information. TBH the reason begs for Unbound Calculation or Query calculation. IMVHO, storing this information is unnecessary and lot of hassle. Set it to Unbound, set the format at runtime, everything will be okay !

I tried something like this but then I ran into the fact that the calculation is also based on another field, so how would I go about that in a single column on the query?

 

[pr2-eugin]

Could you show what you tried, maybe we could give a better solution; if in case it is not efficient.

 

[GendoPose]

My current expression is this

HOURSCALC: FormatDecimalTime([TOTAL JOB QTY]/[RUN RATE])

However I still can't set the totals to Sum on the PivotChart view, it only gives me the option of count.

The code for FormatDecimalTime is this;

Function FormatDecimalTime(DecimalTime As Single) As String
    Dim lngHours As Long
    Dim sngCalculated As Single
    sngCalculated = DecimalTime / 24#
    lngHours = Hour(sngCalculated) + _
    24 * Int(sngCalculated)
    FormatDecimalTime = Format(lngHours, "00") & ":" & _
    Format(sngCalculated, "nn:ss")
End Function