Access report genrates wrong result

[RayV]

I have a report based on a query that summarizes data by county that is
giving me the wrong result.

In the county header I have a text box with this control source
=Avg([stsyear1dol])

The data is
Null
2698
Null
....
1750
Null
1337
Null
....
Null

The report is calculating the average as 1930. A group by query on the
original dataset give me the correct average of 1928.33333.

The format of the text box in the report is set to General Number and
the decimal places is set to '0'. The table underlying the query has
the field size set to double.

Any ideas as to why the report is giving me the wrong answer?

 

[RayV]

I have a report based on a query that summarizes data by county that is
giving me the wrong result.

In the county header I have a text box with this control source
=Avg([stsyear1dol])

The data is
Null
2698
Null
...
1750
Null
1337
Null
...
Null

The report is calculating the average as 1930. A group by query on the
original dataset give me the correct average of 1928.33333.

The format of the text box in the report is set to General Number and
the decimal places is set to '0'. The table underlying the query has
the field size set to double.

Any ideas as to why the report is giving me the wrong answer?

Follow up info:

The answer is wrong whenever calculating the average generates any
decimals. One of the counties generates an average of 1607.0000 and
this is displayed correctly on the report. It seems to be rounding to
the nearest ten.

 

[Douglas J. Steele]

See whether forcing conversion to a number type that supports decimals
helps:

=Avg(CSng([stsyear1dol]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a report based on a query that summarizes data by county that is
giving me the wrong result.

In the county header I have a text box with this control source
=Avg([stsyear1dol])

The data is
Null
2698
Null
...
1750
Null
1337
Null
...
Null

The report is calculating the average as 1930. A group by query on the
original dataset give me the correct average of 1928.33333.

The format of the text box in the report is set to General Number and
the decimal places is set to '0'. The table underlying the query has
the field size set to double.

Any ideas as to why the report is giving me the wrong answer?

Follow up info:

The answer is wrong whenever calculating the average generates any
decimals. One of the counties generates an average of 1607.0000 and
this is displayed correctly on the report. It seems to be rounding to
the nearest ten.

 

[RayV]

See whether forcing conversion to a number type that supports decimals
helps:

=Avg(CSng([stsyear1dol]))

The report won't open using that. It gives me 'Invalid use of Null'.

I also tried CDbl() and got the same error.

 

[RayV]

See whether forcing conversion to a number type that supports decimals
helps:

=Avg(CSng([stsyear1dol]))

Changing the control property from
=Avg([stsyear1dol])

to
=Round(Avg([stsyear1dol]),0)

fixes the problem and gives the correct result but why would I need to
do this. I have the field size in the original table set to Double and
in the text box I have it set to general number and 0 decimal places.
What would cause the report to automatically round to the nearest ten.

 

[RayV]

See whether forcing conversion to a number type that supports decimals
helps:

=Avg(CSng([stsyear1dol]))

Changing the control property from
=Avg([stsyear1dol])

to
=Round(Avg([stsyear1dol]),0)

fixes the problem and gives the correct result but why would I need to
do this. I have the field size in the original table set to Double and
in the text box I have it set to general number and 0 decimal places.
What would cause the report to automatically round to the nearest ten.

Yeah, yeah bad form to answer my own post but Itried something else
since I don't feel like retyping all of the formulas.

If I leave the decimal places setting of the report at '0' and change
the format property from 'General Number' to '#' it stops rounding to
the nearest ten.

Again why would this happen?