iif functin in report

[polparrot via AccessMonster.com]

I have a report that has runs off of a query of invoices by user specified
date range. The report lists each product with a calculated field of the sum
of the quantity [SOQ] giving me total items sold. I am then creating another
calculated field to determin the number of cases of product sold by using
this iif statement: =IIf(([SOQ]<[UPC]),0,(Int(([SOQ])/[UPC]))) where [UPC] is
the units per case. My problem is that the results are incorrect, or
returning TRUE VALUE more often than not.
For example:
1st item correct, 2nd incorrect, 3rd and 4th incorrect, 5th correct, 6 7 & 8
incorrect, 9 & 10 correct, next 6 correct, next 1 incorrect, next 4 correct ..
......
I have looked at the rusults of the query and manually done the calculations
and I don't find anything amiss in the data.
Any ideas or suggestions anyone?

 

[John Spencer]

Is SOQ or UPC ever null (blank)? Is UPC ever Zero?

Just in case any of the above could be true, I would use an expression
like the following.

= IIF(SOQ is Null Or UPC is Null or UPC = 0 OR SOQ<UPC,0, Int(SOQ/UPC))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[polparrot via AccessMonster.com]

Thank you John for replying;

Neither SOQ or UPC should ever be null but I did restructure the iif
statement as you suggested. I am still getting the exact same results as
before though. Do you have any other suggestions?

Thanks,
Polly

 

[John Spencer]

Is SOQ calculated in the query? Or is it calculated in the report?

IF you display SOQ and UPC on the report in separate controls, are the
values correct.

What is the expression for calculating SOQ and where is the calculation
taking place?



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[polparrot via AccessMonster.com]

SOQ is calculated in the report. It is: =Sum[Quantity] with Quantity coming
from the table. UPC is not calcutated it comes straight from the table.

Both SOQ and UPC are displayed in the report in seperate controls.

I have manually checked the data from the table to verify my sums and they
are correct. It just isn't evaluating them correctly. I have even tried
reversing the conditions of the iif function
from =IIf(([SOQ]<[UPC]),0,(Int(([SOQ])/[UPC])))
to =IIf(([SOQ]>[UPC]),(Int(([SOQ])/[UPC])),0) but still come up with the
same result.

Thanks for any help,
Polly

 

[John Spencer]

Try Substituting
Sum([Quantity]) for SOQ

=IIf(Sum([Quantity])<[UPC],0,Int(Sum([Quantity])/[UPC]))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County