Data sheet decimal points

[Clem]

I have added a totals line to a datasheet. One column had numerical values for which I wish to calculate an average. No problem with this but the average comes out something like 13.85746354657. How can I limit the number of decimals places to two?

 

[plog]

Use the format function: http://www.techonthenet.com/access/functions/numeric/format.php

 

[Clem]

Plog, thanks but I'm afraid I am a bit lost. Where do you put the 'Format Function'? When you select design view for the query which runs to select the data, the totals line is not evident. The Totals line is only evident when the query presents results. You cannot edit the cell where the average is displayed except to select another 'Total' such as 'Sum', 'Count', 'Maximum' etc.

 

[plog]

In the section that has 'Group By', 'Sum', 'Count' etc., select 'Expression'. Then above, you put this around your field name (for reference point, I will use 'YourFieldName' as the name of your field:

DataAvg: Format("Standard", Avg([YourFieldName]))

 

[Clem]

Plog, thanks again but I am still struggling.

I open my query in Design View
I click on Totals on the Show/Hide box on the ribbon
I select Expression from the drop down list in the Group By line in my column
I Type in DataAvg: Format("Standard", Avg([KS1 RDG PTS]))
I run query
The datasheet Results change the column header to DataAvg and the cells in the datasheet have the word Standard in cells where there is data and #Error where there is no data

 

[plog]

I'm sorry I did it backwards, your field should be the first argument in the Format function:

DataAvg: Format(Avg([KS1 RDG PTS]), "Standard")

You are still going to get an error where there is no data, because the average of no data is undefined.

 

[Clem]

Hi again. You are being very patient and thanks for the help so far. Your expression works in that in the datasheet field where there is data it enters a number with two decimal places, fine. But what I want is an average at the bottom of the column which has calculated the average for the whole column.

One can do this by running the query and clicking on the totals icon in the Records area of the Ribbon. This adds another line to the query results after the * line. You can then go to your column put your cursor on the bottom of the column where you need the average and select average from a drop down list. There is no option for Expression. If you select Average it calculates the average for the column but provides 13 decimal places! This is my problem.