Someone Please Help?

S

Sal_Garcia

In my Control Source I have

=[Qty 2]*[Master Type Factor]

My problem is this. If [Qty 2] is left blank it doesnt show a 0. I want it to
show a 0 for it to work since this is linked to other queries, but will not
work if blank. Is there anything I can add to this current query to make is
show 0 if [Qty 2] is left blank???
 
J

John Spencer

Try using an IIF statement to return 0 if Qty 2 is null.

IIF([Qty 2] is null,0,[Qty 2]) * [Master Type Factor]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
J

John W. Vinson

In my Control Source I have

=[Qty 2]*[Master Type Factor]

My problem is this. If [Qty 2] is left blank it doesnt show a 0. I want it to
show a 0 for it to work since this is linked to other queries, but will not
work if blank. Is there anything I can add to this current query to make is
show 0 if [Qty 2] is left blank???
Click to expand...

The problem is that null - blank - is NOT equal to zero. Null means "this
value is unknown, unspecified, undefined" - and [Master Type Factor]
multiplied by an unknown value gives an unknown value.

There's a builtin function to deal with this though - NZ() for Null To Zero.
Try

=Nz([Qty 2]) * [Master Type Factor]

This will convert a null value to 0, and leave non-Null values alone.

NZ() has an optional second argument, the value to be returned; if you don't
specify it it returns 0 for a Number or Currency field and an empty string for
a Text field, but if you want a Null value to be shown as 100, or "Not
Available", or anything else you can use that value as the second argument.
 
Top