Report calculation

  • Thread starter Thread starter HubbyMax
  • Start date Start date
H

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.
 
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?
 
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?

Duane Hookom said:
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


HubbyMax said:
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.
Click to expand...
Click to expand...
 
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?

Duane Hookom said:
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


HubbyMax said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

John Spencer said:
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?

Duane Hookom said:
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.
Click to expand...
Click to expand...
.
Click to expand...
 
Solved! Changed my query calc to

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