Null Values

[DBFIN]

I have a query that reads from a table where the fields have many null values. On of the query fields is a calculation that multiplies several fields in the table. If the table field has a null value, the calculation results in an error. Is there a way to quickly correct the null value issue ? Can nulls be converted to 0 easily with a standard function ?

 

[bob fitz]

Take a look at the Nz() function

 

[DBFIN]

The Nz function is a very powerful function. How can I use it to interpret all null values in a query field as 1.0. What is the syntax if I use the standard Nz function ?

 

[DBFIN]

I understand the syntax now, you can disregard my previous posting. Thanks !!

 

[bob fitz]

In the query design window: Drag the field from the table to the grid. Change the name of the field to something like:
NoNulls:Nz([MyFieldName],0)
You can replace the zero in that expression with anything that you want returned if the field is null.

 

[DBFIN]

Thanks, I understand the syntax now but have a very subtle problem. I have the following field: (Nz([Field1],0)+Nz([Field2],0))/Nz([Field3,1). I tested the numerator to make sure all null values for Field1 and Field2 are 0, however when these fields are embedded in the above calculation the result is an error: #NUM!. This error occurs only when Field1 and Field2 are null before the conversion.

 

[DBFIN]

I think I know what the issue is now, something wrong in the denominator calculation, the Nz syntax works great.

 

[bob fitz]

Try:
NewField: Nz([Field1],0)+Nz([Field2],0)/Nz([Field3],1)

 

[ButtonMoon]

I have a query that reads from a table where the fields have many null values.

Why? If you plan to treat these nulls as zeros then you might reconsider whether it is worth permitting them into the table in the first place. Nulls inevitably complicate query logic and will have other undesirable effects on the results you get out of the database.

 

[DBFIN]

Totally agree, the data is provided to me by an independent team that should not have permitted any null values in the first place.