Querying a table, I want null values to be zero 1

[Sandman007]

I am querying a table and creating a new one. I want null values to come through as zero because I need a number in every field.

 

[Craig0201]

You need to use the Nz function.

Nz([Name of field],0)

Will return the value of the field if it has one or 0 if null. Magic!

Craig

 

[Sandman007]

Craig -
Where do I put this "Nz" function? In the Query criteria field under the column..or the SQL code?

 

[fynder]

The NZ function goes on the field line of your query grid. The function "Nz(variant[, valueifnull])" returns a VARIANT type variable. If you are interested in a Long Integer value instead of a variant you can use an "Immediate IF" function. Assume the Field Name is "Budget". To create an "immediate if" to create a zero in place of a null and then to use that to create a new table, you could put this into the filed line for the Budget field of the query grid where "Budget" is originally a long integer. BUDGET:iif(isnull(Budget),0,Budget)
"Databases That Work The Way You Do!"