How to format query field when using expression 1

[srast]

Hello all.
I have a calculated field in a query which I want to format to show only only whole numbers. Problem is, I need to test if another field used in the calculation is null, and if so, leave the field blank, NOT put in a zero. If I use an IIf statement to test for this, as so:
Lbs Sold: IIf([Qty]>0,([Qty]*([OzPerUnit]/16)*[Percentage])*1.02," &quot
the format I put on the field doesn't carry through. Any ideas?
The reason I need to do this is because a report is based on this query, and I need that field to remain blank if there is no number generated, and only whole numbers if there is.
Any assistance would be GREATLY appreciated.
Steve

 

[Terpsfan]

This will round your Qty to an integer.

Lbs Sold: IIf([Qty]>0,Round(([Qty]*([OzPerUnit]/16)*[Percentage])*1.02)," &quot

 

[srast]

Omega36:
Thanks for your response. Works great. Just the function I was looking for.
Thanks!
Steve

 

[srast]

Omega36:
One more question. I have a report based on that query. I want to make a textbox, with the ControlSource = Sum([Lbs Sold]). When I do that, I get the following error:
"The expression is typed incorrectly, or is to complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I assume that is because the value Lbs Sold might be an empty string.
Any ideas for a workaround?
Thanks.
Steve

 

[Terpsfan]

You're right. You have to get rid of the zero-length string. Access can't sum a field with a zero-length field. I'd suggest putting in a NULL value instead of "". Then you should still get a summation.

Lbs Sold: IIf([Qty]>0,Round(([Qty]*([OzPerUnit]/16)*[Percentage])*1.02),Null)

 

[srast]

Thanks Omega36!
Really appreciate it. With the Null instead of an empty string, the summation works fine.
Thanks again!
Steve