Nz function not working!

[gregatvrm]

The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[Jeanette Cunningham]

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham

 

[Klatuu]

You are asking for a character "0" rather than a number 0. I don't know if
that makes a difference to you. You usually want to return the same data
type as the field or variable you are using in the Nz function.

Are you returning any rows? Like they field is blank, but there is a row
count?

If you are getting no rows, then the Nz is not the problem. Your query may
not work at all.

 

[KARL DEWEY]

You are using quotes around the zero and that makes it text! Drop the quotes.

 

[Klatuu]

What if [Kids] is a text field?
The Nz function works with any data type. And it is good practice to use a
data type in the Nz compatible with the field or variable being tested.
--
Dave Hargis, Microsoft Access MVP

You are using quotes around the zero and that makes it text! Drop the quotes.
--
KARL DEWEY
Build a little - Test a little

The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[gregatvrm]

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham

The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[Klatuu]

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham

The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[gregatvrm]

Klatuu,

Using your syntax returns a blank row and no 0. When using the original
statement posted it did not return a row.

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[Klatuu]

First get your query working. It is blank because it is not finding any
matches based on your criteria.
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Using your syntax returns a blank row and no 0. When using the original
statement posted it did not return a row.

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

:

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[gregatvrm]

The query has been working until now. It is searching the table trying to
find if there are any kids currently in our program. We currently have no
kids and now it only shows a blank row. I need to have this blank row to show
a 0 for my report to work.

Am I missing something here?

First get your query working. It is blank because it is not finding any
matches based on your criteria.
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Using your syntax returns a blank row and no 0. When using the original
statement posted it did not return a row.

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP


:

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

:

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[Klatuu]

Since you have no kids, you will get no matching rows, so there is nothing to
sum.
--
Dave Hargis, Microsoft Access MVP

The query has been working until now. It is searching the table trying to
find if there are any kids currently in our program. We currently have no
kids and now it only shows a blank row. I need to have this blank row to show
a 0 for my report to work.

Am I missing something here?

First get your query working. It is blank because it is not finding any
matches based on your criteria.
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Using your syntax returns a blank row and no 0. When using the original
statement posted it did not return a row.


:

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP


:

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

:

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[gregatvrm]

Is there a way to return a 0 in the blank row. Isn't this what the Nz
function does? Would using the IIF statement be a better of getting this to
work?

Since you have no kids, you will get no matching rows, so there is nothing to
sum.
--
Dave Hargis, Microsoft Access MVP

The query has been working until now. It is searching the table trying to
find if there are any kids currently in our program. We currently have no
kids and now it only shows a blank row. I need to have this blank row to show
a 0 for my report to work.

Am I missing something here?

First get your query working. It is blank because it is not finding any
matches based on your criteria.
--
Dave Hargis, Microsoft Access MVP


:

Klatuu,

Using your syntax returns a blank row and no 0. When using the original
statement posted it did not return a row.


:

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP


:

Thanks all,

Tried all the above and still nothing. I am using numbers in the table not
text. What I need is to sum "Kids" field and if null return a 0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

:

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


The Nz function used in the following query SQL statement returns nothing,
not even a blank box. I need it to return a 0. Thanks for all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND ((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));

 

[Rick Brandt]

Is there a way to return a 0 in the blank row. Isn't this what the Nz
function does? '

No. Nz() changes the values of *fields* that would otherwise be null in
existing *rows*. It does not produce rows where none exist.

 

[Michel Walsh]

In addition to Rick's answer, and if you are using a report, can't you use
the On No Data event procedure to handle the case where there is just no
data to be printed?


Vanderghast, Access MVP

Is there a way to return a 0 in the blank row. Isn't this what the Nz
function does? Would using the IIF statement be a better of getting this
to
work?

Since you have no kids, you will get no matching rows, so there is
nothing to
sum.
--
Dave Hargis, Microsoft Access MVP

The query has been working until now. It is searching the table trying
to
find if there are any kids currently in our program. We currently have
no
kids and now it only shows a blank row. I need to have this blank row
to show
a 0 for my report to work.

Am I missing something here?

:

First get your query working. It is blank because it is not finding
any
matches based on your criteria.
--
Dave Hargis, Microsoft Access MVP


:

Klatuu,

Using your syntax returns a blank row and no 0. When using the
original
statement posted it did not return a row.


:

That would not be correct. The correct syntax is:
SELECT Sum(Nz([STATS.Kids], 0)) AS SumOfKids

But, does your query return any rows at all?
--
Dave Hargis, Microsoft Access MVP


:

Thanks all,

Tried all the above and still nothing. I am using numbers in
the table not
text. What I need is to sum "Kids" field and if null return a
0. Nothing
happens even putting in
SELECT Nz(Sum([STATS.Kids])) AS SumOfKids.

I am using Access 2003 also.

:

Hi,
Instead of Nz([Kids], "0")
use
Nz([Kids], 0)

Jeanette Cunningham


message
The Nz function used in the following query SQL statement
returns nothing,
not even a blank box. I need it to return a 0. Thanks for
all your help.

SELECT Nz([Kids],"0") AS Expr1
FROM STATS, RESIDENT INNER JOIN History ON
RESIDENT.RESID=History.RESID
WHERE (((History.ExitDate) Is Null) AND
((RESIDENT.Sex)="f") AND
((RESIDENT.Designation)="Single With Children") AND
((RESIDENT.STEPNo)<"4-Alpha House"));