Nz

[oklahoma84]

When i make a table into a crosstab, there are blanks displayed. How would I use the Nz function to fix this, or is there another way?

 

[Moniker]

Nz, short for NullZero, is not something to remove a blank, but rather something to change the value of a null to something more meaningful.

Nz(field/variable name here, value of field/variable name if it's null)

If you just don't want blanks, you'd use that in the definition of the crosstab, as in WHERE YourFieldName Is Not Null.

Nz is mostly used to check for blanks in required fields, or to change null values into a number in a math calculation. For example:

Value = 12 + Nz(SomeUserInput,0)

If SomeUserInput is Null, that would return an error without the Nz. With the Nz, it's converted to a 0, and all is good in the world. Or calculation.

 

[Jon K]

Assuming the values are numeric and you want the crosstab query to display a numeric zero for each blank, you can use the Nz() function in the Transform Clause of the SQL Statement like this:-

TRANSFORM Nz(.....)+0 AS .....
SELECT ..........
FROM ..........
GROUP BY ..........
PIVOT ..........
.

 

[Inforat]

Thank you Jon!!! This is exactly what I needed too!! My Nz function didn't have the '+0' so my crosstab query has been driving me nuts this week. Thanx a million.