Does Nz function return a numeric value or string?

[Ymesei]

I'm having trouble with a result set that returns null values that I want to convert to a zero numeric value. If memory serves me right, the following function would return a zero if applied to a null value field.

Nz([field1],0)

When I use this in a query, the field returns a 0. But for some reason, when I try to calculate on this field, it seems to treat it like it's a "0" text string . . .

What's happening is I have two tables that I have created a RIGHT JOIN in my query. The result recordset has some null value fields because I want all records to show for one table even though there is no matching record in the other table. I want these null values to return a 0 value, so I can run a report that shows the sum of the two fields for all records.

I know I've used this solution in the past, but I'm thinking there must be something different about the way I'm using the Nz function that is causing the calculation not to work.

Thanks in advance for any help you can give.

Will

"Never laugh at ignorance. You may not know what your laughing at."

 

[earthandfire]

Nz returns a variant, so you could wrap it in CInt eg

CInt(Nz([field1],0))


Hope this helps

 

[Golom]

NZ returns 0 (a numeric 0) if the first argument is a numeric data type. If your first argument is text then it will coerce the second argument to text so that your 0 as the second argument will be coerced to '0' (i.e. a text data type). Use the Val function around the NZ call to ensure that the type is numeric.

 

[Ymesei]

Thank you all for your help. Wrapping the Nz function in Val() worked perfectly. Thank you Golom for explaining exactly how Nz determines the type.

"Never laugh at ignorance. You may not know what your laughing at."