[AccessD] NZ for Null

Steve,

    Don't you think data type enforcement function wrapped around overall  expression for the calculated field should suffice, instead of converting each element separately ?

    Example (RDate is date type field while RNum & SNum are number type fields):
    (a) Date type output:
        CDate(Nz([RDate],Date())+Nz([RNum],0))
    (b) Number type output:
        Val(Nz([RNum],0)+Nz([SNum],0))

    Note - It is also observed that the output of a calculated field in a query is a string only when it is a single element Nz() expression like 
Nz([RDate],Date()) or Nz([RNum],0). If there is interaction involving mathematical operators, the output is seen to be a number even without application of a data type conversion function. However, for date type, use of CDate() becomes necessary so as to display dates rather than equivalent date serials. All in all, it would be a safe practice to apply the conversion as per (a) & (b) above universally when using Nz() function. Similar precaution becomes necessary in case of domain aggregate functions.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Steve Schapel 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, June 21, 2007 02:34
  Subject: Re: [AccessD] NZ for Null


  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.