Jump to content

Rounding / Decimal Places In Ms Access Reports

Recommended Posts

Posted (edited)

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 by Grahamfff

Share this post


Link to post
Share on other sites

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).

 

Share this post


Link to post
Share on other sites

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!)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.