Rounding / Decimal Places In Ms Access Reports

[Grahamfff 10]

I have an Access database report in which there is a field with  the control set to ="Total time spent: " & Sum([time spent]/60) & " hours"

 

So if the value in the total time spent total is 90, then this field will return: Time time spent: 1.5 hours

 

But if the total time spent is 80, then it returns: Time time spent: 1.3333333333 hours

 

How can I limit the number of decimal places showing?

 

(Also, I'm not too bothered by it showing decimal fractions of an hour, but it anyone can show me how to make it show hours and minutes instead, that would be even better!)

 

Thanks

Edited July 30, 2019 by Grahamfff

[altus 533]

Use the Text function.

 

="Total time spent: " & Text(value, "##0.##) & " hours"

 

Will give Total time spent: 1.33 hours.

 

="Total time spent: " & Text(value, "[H]:MM) & " hours"

 

Will give Total time spent: 1:20 hours. In the later example you need the square brackets to have 24 hours or more. Also, Excel treats date/times as a number with days being the whole number part so you need to scale your values so that 24 hours = 1 (so divide by 1440 instead of 60).

 

[Grahamfff 10]

I'm afraid this gives me me error message - missing opertaor/

 

(Also, you referred to Excel whereas I'm using Access - maybe it makes no diference though!)

[altus 533]

Sorry, my bad. Access has a Format function which does something similar. Also, I missed the closing " from the format strings in my examples.