[AccessD] NZ for Null

Steve Schapel miscellany at mvps.org
Wed Jun 20 16:04:48 CDT 2007


Virginia,

Sorry, I do see now that you specifically mentioned this is in "the 
query for the report", I got confused by the context of some of the 
other posts.  So, I recommend, in the query...

YourCalculatedField: 
Val(Nz(tbl_InventoryCurrent!Units,0))-Val(Nz(qry_nonpurchase!SumOfUnits,0))-Val(Nz(qry_purchase!SumOfUnits,0))

... and then, as per Dan's recommendation, set the Format property of 
the textbox on the report to Standard, Decimal Places 0.

Regards
Steve


Steve Schapel wrote:
> Virginia,
> 
> I agree with Arthur and Dan.  I can't see the beginning of this thread, 
> so I'm not sure whether this expression is being used in code, or in a 
> calculated field in a query, or in the Control Source of a calculated 
> textbox on your report.  Anyway, something else to bear in mind is that 
> the Nz() function returns a string, so it is sometimes tricky.  If you 
> want the result to still behave as a number (e.g. to apply number 
> formats etc), you have to convert it back to a numerical value again, 
> typically by wrapping it inside a Val() function.



More information about the AccessD mailing list