Nz or Iif

[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!

 

[Jeff Boyce]

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]

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

 

[John W. Vinson]

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!

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?

 

[Kristibaer]

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 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!

 

[Kristibaer]

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!

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 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!

 

[Kristibaer]

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)

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!

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?

 

[Kristibaer]

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

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!

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?

 

[Kristibaer]

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

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!

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?