I'm stuck with decimals

[Crevalle]

I've done three forum searches, but nothing seems to be working. I have a very simple select query (which feeds a report of mine) that has a field that uses the "Avg" function. However, the averages are 2.774645376378 or 10.7369859837, etc. I would like just two decimal places to show up.

I've tried using the "format" expression, but my syntax must be incorrect.

Would it possibly be easier to format the number in the report? I went to the report, clicked "properties" on the field I reference above, and there is no option for decimals.

Please help!

 

[RickSpr]

1. In your query, replace Avg(...) with Round(Avg(...), 2).

-or-

2. The report control does have a Decimal Places property. If you can't find it, be sure you have the All tab selected in the properties sheet.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Crevalle]

RickSpr--The average I use is in the group-by field ("total"). It isn't a formula.

Also, in the report, there is no field for "decimal" or anything similar in the "all" tab (for the relevant text box). What am I doing wrong?

 

[Crevalle]

Sorry--I was looking at the properties of the "label." My bad--thanks!

 

[RickSpr]

Try this: Change the Total: line to "Expression", and put the following in the Field: line:
Round(Avg([fieldname]), 2)

I don't see how you can not have a Decimal Places property. If it's a Report and you've selected the TextBox control, the first 4 properties should be Name, Control Source, Format, and Decimal Places. (Access 2000, but I don't think the version matters.) You're not talking about a Data Access page are you?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Ed2020]

Be careful with the MS Access round function. It performs Bankers rounding, not natural rounding so will give unusual results sometimes.

I prefer to hook into the Excel Worksheet round function from VBA.

Ed.

Please do not feed the trolls.....