Nz or Iif

  • Thread starter Thread starter Kristibaer
  • Start date Start date
K

Kristibaer

I am in need of an expression that will return a zero (0) if a field value is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
 
When I want to see a 0 when a value is Null, I use:

Nz([MyField],0)

Please post the SQL statement that includes your Nz() function...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I actually figured it out by adding an addition column and tweaking the
expression:

COUNT: Nz([COUNT SUMS].[SumOfCount Quantity],0)

Seems to be returning the necessary results.

Kristibaer
 
I am in need of an expression that will return a zero (0) if a field value is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...

What you posted will do that... if it's used in the correct context. Where did
you put the expression? Perhaps on the Criteria line? That would require that
a field be equal to 0. What's the actual SQL of the entire query?
 
Jeff Boyce said:
When I want to see a 0 when a value is Null, I use:

Nz([MyField],0)

Please post the SQL statement that includes your Nz() function...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kristibaer said:
I am in need of an expression that will return a zero (0) if a field value
is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...
Click to expand...
 
thanks Jeff! Actually I played a little more and found that if I created
another column with the expression as follows, the results returned almost
exactly what I need:

COUNT: Nz([COUNT SUMS].[SumOfCount Quantity],0)

Thanks!

Jeff Boyce said:
When I want to see a 0 when a value is Null, I use:

Nz([MyField],0)

Please post the SQL statement that includes your Nz() function...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kristibaer said:
I am in need of an expression that will return a zero (0) if a field value
is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...
Click to expand...
 
I used it in creating a new column called " COUNT"and added the expresion as
follows:

COUNT: Nz([COUNT SUMS].[SumOfCount Quantity],0)

I used it as I would when using data from mutliple columns in my query to
calculate a new value (i.e. DATA: =sum(a+b)

John W. Vinson said:
I am in need of an expression that will return a zero (0) if a field value is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...

What you posted will do that... if it's used in the correct context. Where did
you put the expression? Perhaps on the Criteria line? That would require that
a field be equal to 0. What's the actual SQL of the entire query?
Click to expand...
 
Here's the SQL:

SELECT phymast.item, phymast.descrip, Nz([COUNT SUMS].[SumOfCount
Quantity],0) AS [COUNT], phymast.onhand, ([COUNT]-[phymast].[onhand]) AS
Variance, [COUNT SUMS].lastcost, [COUNT SUMS].avgcost
FROM phymast LEFT JOIN [COUNT SUMS] ON phymast.item = [COUNT SUMS].item
WHERE (((phymast.stocking)=-1))
ORDER BY phymast.item;

I used the expression in the detail of a new column as I would when trying
to calculate data when trying to yeild resukts from other columns in the
query (i.e. DATA: =SUM(a+b)). This is my column heading:

COUNT: Nz([COUNT SUMS].[SumOfCount Quantity],0)

So far. so good.

Thanks,
Kristi




John W. Vinson said:
I am in need of an expression that will return a zero (0) if a field value is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...

What you posted will do that... if it's used in the correct context. Where did
you put the expression? Perhaps on the Criteria line? That would require that
a field be equal to 0. What's the actual SQL of the entire query?
Click to expand...
 
Here's the SQL:

SELECT phymast.item, phymast.descrip, Nz([COUNT SUMS].[SumOfCount
Quantity],0) AS [COUNT], phymast.onhand, ([COUNT]-[phymast].[onhand]) AS
Variance, [COUNT SUMS].lastcost, [COUNT SUMS].avgcost
FROM phymast LEFT JOIN [COUNT SUMS] ON phymast.item = [COUNT SUMS].item
WHERE (((phymast.stocking)=-1))
ORDER BY phymast.item;

I used the expression in the detail of a new column as I would when trying
to calculate data when trying to yeild resukts from other columns in the
query (i.e. DATA: =SUM(a+b)). This is my column heading:

COUNT: Nz([COUNT SUMS].[SumOfCount Quantity],0)

So far. so good.

Thanks,
Kristi

John W. Vinson said:
I am in need of an expression that will return a zero (0) if a field value is
null. I tried the following expression, but instead of returning 0 in the
null values, it eliminated them altogether:

Nz([COUNT SUMS].[SumOfCount Quantity],0)

Is there an Iif expression that will replace the null values with zero
instead of eliminating those records where this value is null?

Thank you!
Click to expand...

What you posted will do that... if it's used in the correct context. Where did
you put the expression? Perhaps on the Criteria line? That would require that
a field be equal to 0. What's the actual SQL of the entire query?
Click to expand...
 
Back
Top