Report calculation

[HubbyMax]

I have a report that I am having trouble with. The report is controled by
a query. In the query I have a calculation.

AVG((([OUnitPrice]+[NUnitPrice])/2)*[QuantityIssued]]) AS AVGCOST

I use this calculation to fill a order cost of an item on the report. I
then have a text box on the report with the calculation, =SUM([AVGCOST])
to show the total cost.

The problem is that if you manually add all the results for the query
calculations the total is 423.25. When the calculation is done by the
report itself it totals 423.24. Any ideas why this happens? It does not
happen for all calculations on the report, most are equal with a manual
addition.

 

[Duane Hookom]

Are you aware there may be slight rounding issues? Do you understand when
viewing the data to 2 decimal places, you aren't seeing the entire, accurate
values?

 

[HubbyMax]

Yes I am aware of this. I know that this differance is caused by the 2
decimal places. The company this report is for insists on working with
avarage costs and insists that the total shown should equal a manual
calculation. Once the AVGCOST figure is entered onto the report is there any
way to restrict it to 2 decimal places and be able to add those figures as 2
decimal figures?

Are you aware there may be slight rounding issues? Do you understand when
viewing the data to 2 decimal places, you aren't seeing the entire, accurate
values?
--
Duane Hookom
Microsoft Access MVP

I have a report that I am having trouble with. The report is controled by
a query. In the query I have a calculation.

AVG((([OUnitPrice]+[NUnitPrice])/2)*[QuantityIssued]]) AS AVGCOST

I use this calculation to fill a order cost of an item on the report. I
then have a text box on the report with the calculation, =SUM([AVGCOST])
to show the total cost.

The problem is that if you manually add all the results for the query
calculations the total is 423.25. When the calculation is done by the
report itself it totals 423.24. Any ideas why this happens? It does not
happen for all calculations on the report, most are equal with a manual
addition.

 

[John Spencer]

You might try using the round function to truncate the number down to 2
decimal digits. Or multiply every figure by one hundred, use int to truncate
or CLNG to round up or down and do the calculation. Then Divide by 100.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Yes I am aware of this. I know that this differance is caused by the 2
decimal places. The company this report is for insists on working with
avarage costs and insists that the total shown should equal a manual
calculation. Once the AVGCOST figure is entered onto the report is there any
way to restrict it to 2 decimal places and be able to add those figures as 2
decimal figures?

Are you aware there may be slight rounding issues? Do you understand when
viewing the data to 2 decimal places, you aren't seeing the entire, accurate
values?
--
Duane Hookom
Microsoft Access MVP

I have a report that I am having trouble with. The report is controled by
a query. In the query I have a calculation.

AVG((([OUnitPrice]+[NUnitPrice])/2)*[QuantityIssued]]) AS AVGCOST

I use this calculation to fill a order cost of an item on the report. I
then have a text box on the report with the calculation, =SUM([AVGCOST])
to show the total cost.

The problem is that if you manually add all the results for the query
calculations the total is 423.25. When the calculation is done by the
report itself it totals 423.24. Any ideas why this happens? It does not
happen for all calculations on the report, most are equal with a manual
addition.

 

[HubbyMax]

The round function is exactly what I would like to use. My problem is that I
can not find any help in the access help to show me how to do it. Can anyone
convert my current code to apply the round function? I would also use the
other suggestion but am not sure how to do it. I am somewhat of a beginner
here.

You might try using the round function to truncate the number down to 2
decimal digits. Or multiply every figure by one hundred, use int to truncate
or CLNG to round up or down and do the calculation. Then Divide by 100.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Yes I am aware of this. I know that this differance is caused by the 2
decimal places. The company this report is for insists on working with
avarage costs and insists that the total shown should equal a manual
calculation. Once the AVGCOST figure is entered onto the report is there any
way to restrict it to 2 decimal places and be able to add those figures as 2
decimal figures?

Are you aware there may be slight rounding issues? Do you understand when
viewing the data to 2 decimal places, you aren't seeing the entire, accurate
values?
--
Duane Hookom
Microsoft Access MVP


:

I have a report that I am having trouble with. The report is controled by
a query. In the query I have a calculation.

AVG((([OUnitPrice]+[NUnitPrice])/2)*[QuantityIssued]]) AS AVGCOST

I use this calculation to fill a order cost of an item on the report. I
then have a text box on the report with the calculation, =SUM([AVGCOST])
to show the total cost.

The problem is that if you manually add all the results for the query
calculations the total is 423.25. When the calculation is done by the
report itself it totals 423.24. Any ideas why this happens? It does not
happen for all calculations on the report, most are equal with a manual
addition.

.

 

[HubbyMax]

Solved! Changed my query calc to

ROUND((([OUnitPrice]+[NUnitPrice]/2)*[QuantityIssued],2) AS AVGCOST