NZ function

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

hello all,

I am currently using the Nz function to populate the null values with zeros
so that I can build calculated fields in queries. The NZ function returns a
text value, and when I try to add those values it treats them as text. For
example:

Field1: nz([Rev],0)

Field2: nz([cost],0)

Margin: [Field1]-[Field2]

If the there was a null value in fields 1 and 2 the margin calculation
returns two zeros or "00."

Is there another function that I should be using? Is there a way to change
the formats so that field1=1 and field2 =2 would create a margin number of -1
not 12? I have tried changing the format of the field to Standard or general
number and that does not solve the problem.

Thanks for any help!

Brennan
 
NZ is the correct function, but when used in a query it usually returns a
string. So, you may have to use the Val function or one of the conversion
functions (Cint, CLng, CDbl, or CCur) to force the field to return a number of
the proper type.

Field1: Val(Nz([Rev],0))

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