Rounding calculated fields

[BrockLanders]

I have an Access query with a calculated field of average weight. It returns numbers like 18.26598. I want it to round to the nearest whole number, so the above number would be 18. You cannot set the decimal places of calculated fields, not even given the option. I tried creating a new query based on the first query, but when I set the decimal places property to anything, I get a division by zero error.

Any ideas?

 

[PaulBricker]

If you are using A2000 have you looked at the Round Function. You would just leave the decimal argument blank or set to 0.

Paul

 

[BrockLanders]

I am using A2000, but could not seem to get the Round function to work

 

[PaulBricker]

What does your calculation look like.

Paul

 

[BrockLanders]

In the Criteria row i have:
Round([Avg Wt], 0)

 

[PaulBricker]

Sorry, what I should have asked is what calculation do you use to get your [Avg Wt] field. Can you use
Round((Field1 + Field2 + Field3/ 3),0) or whatever your calculation is to get what you want.

Paul

 

[Solomons]

Hi All,
I'm having a similar problem. I'm using an SQL query in the record source like,
"Select Sum(markedhours)/24 as MarkedDays From tblMarked where uid='xxxxx'"

How Do I round the values here???
I'm using access 97 and cant seem to find a round function.

Thanx in Advance
Solomons

 

[PaulBricker]

Solomons, it sounds like you want whole days. If you just want whole days, you can use this
"Select Int(Sum(mardedhours)/24)
If there is more than half a day extra, and you want it to round up we'll have to make it up.

Paul

 

[Solomons]

Hi Paul,
Thanx but I'm not looking for whole days. I would really have preferred to display the field as xx days and xx hrs but i'm ready to settle for xx.xx days for now.
Have work out some complicated way to get accurate count of days and hours later.
btw Format() function seems to work for me right now.

Does anyone know how I can convert a field stored in hours to xx days and xx hrs.

Thanx a lot

 

[PaulBricker]

Depending on the exact format you want it goes like this.

"Select Int(markedhours/24) & " days and " & Int(((markedhours/24) - Int(markedhours/24))*24) & " hours" as MarkedDays...


Paul

 

[Solomons]

Thanks a lot Paul that did the trick ..love this place

 

[BrockLanders]

Thanks PaulBricker!
Your calculated field using the Round function worked great. It is a simple solution, which I like.

Thanks much.