NZ function

[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

 

[John Spencer]

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