How to use Nz() in Query field

[Pete VanDusen]

Hi. I posted a question a couple days ago about returning
a zero for a null value (fish captured in nets), and was
told to use the Nz() function. I obviously dont have the
syntax right. Can someone show me an example of code that
does this and where the code actually goes. I assume its
typed into the "Field" under the given column in the
Design View of a Query. I'm still confused about how to
use SQL or what "controls on a form" means so help pages
that I've seen going that route haven't helped me.

For what its worth, here's what I have come up with:

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT[,0]])

Specifically, I want to merge the FISHCOUNT variable from
the GN Fishcount query into my new table and call
it "ZEROCOUNT", where any null values are turned into
zero. By any null values, I mean specific entries that
have criteria met under other columns, but have no
observations under FISHCOUNT. Am I even on the right
track?!! Even code that already works for something else
would be useful if I could decompose it and figure out how
it works. Maybe I have to go into SQL or figure out
what "control on a form means"??

Thanks in advance....

 

[Rick Brandt]

Hi. I posted a question a couple days ago about returning
a zero for a null value (fish captured in nets), and was
told to use the Nz() function. I obviously dont have the
syntax right. Can someone show me an example of code that
does this and where the code actually goes. I assume its
typed into the "Field" under the given column in the
Design View of a Query. I'm still confused about how to
use SQL or what "controls on a form" means so help pages
that I've seen going that route haven't helped me.

For what its worth, here's what I have come up with:

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT[,0]])

You have extra brackets in here. Should be...

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT],0) + 0

Why the plus zero? Because unless you coerce it to a different type, Nz() will
return a string, and not just for the Null-To-Zero values, but for all values in the
column. The "+ 0" tells Access to return a numeric value from the function.

 

[Pete VanDusen]

I tried this and it still doesn't work. It makes me input
a "parameter value" for it to run. And when I type in
something it just returns that number the number that I
entered.

I used the "[FISHCOUNT[,0]])" because I wanted to return a
zero when there is no number. For example, I need to
designate the value to zero when the underlying query
returns no records. It isn't that the query returns
records with certain fields having a NULL value, it is
that the query simply has NO RECORDS to show matching teh
criteria. In that instance, I need to return a value of
zero, so that any math done on the query includes
observations of "0".

Does that make sense?

-----Original Message-----

Hi. I posted a question a couple days ago about returning
a zero for a null value (fish captured in nets), and was
told to use the Nz() function. I obviously dont have the
syntax right. Can someone show me an example of code that
does this and where the code actually goes. I assume its
typed into the "Field" under the given column in the
Design View of a Query. I'm still confused about how to
use SQL or what "controls on a form" means so help pages
that I've seen going that route haven't helped me.

For what its worth, here's what I have come up with:

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT[,0]])

You have extra brackets in here. Should be...

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT],0) + 0

Why the plus zero? Because unless you coerce it to a different type, Nz() will
return a string, and not just for the Null-To-Zero

values, but for all values in the

 

[Pete VanDusen]

I tried to post another reply, but it didn't work I guess,
so I'll try again...

I tried the new code, but it didn't work form me. The zero
after the comma I added because I want to return a zero
for any crossproducts that dont contain a value. In other
words I need to designate the value zero when the
underlying query returns no records. It isn't that
the query returns records with certain fields having a
NULL value, it is that the query simply has NO RECORDS to
show matching the criteria. In that last instance, I need
the control object in the report to return a value of
ZERO. I can't do any math on the numbers as is without
averaging in the zero.

Thanks again!

-----Original Message-----

Hi. I posted a question a couple days ago about returning
a zero for a null value (fish captured in nets), and was
told to use the Nz() function. I obviously dont have the
syntax right. Can someone show me an example of code that
does this and where the code actually goes. I assume its
typed into the "Field" under the given column in the
Design View of a Query. I'm still confused about how to
use SQL or what "controls on a form" means so help pages
that I've seen going that route haven't helped me.

For what its worth, here's what I have come up with:

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT[,0]])

You have extra brackets in here. Should be...

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT],0) + 0

Why the plus zero? Because unless you coerce it to a different type, Nz() will
return a string, and not just for the Null-To-Zero

values, but for all values in the

 

[Rick Brandt]

I tried to post another reply, but it didn't work I guess,
so I'll try again...

I tried the new code, but it didn't work form me. The zero
after the comma I added because I want to return a zero
for any crossproducts that dont contain a value. In other
words I need to designate the value zero when the
underlying query returns no records. It isn't that
the query returns records with certain fields having a
NULL value, it is that the query simply has NO RECORDS to
show matching the criteria. In that last instance, I need
the control object in the report to return a value of
ZERO. I can't do any math on the numbers as is without
averaging in the zero.

Nz() won't do that. A report based on a RecordSet with no records in it will just
show #Error for all of the bound controls. I usually just use the NoData event of
the report to display a message and cancel the report in these circumstances.

 

[Guest]

Sounds like you'll need to use a recordset. As you found,
nz() only handles actual null values.

-----Original Message-----
I tried to post another reply, but it didn't work I guess,
so I'll try again...

I tried the new code, but it didn't work form me. The zero
after the comma I added because I want to return a zero
for any crossproducts that dont contain a value. In other
words I need to designate the value zero when the
underlying query returns no records. It isn't that
the query returns records with certain fields having a
NULL value, it is that the query simply has NO RECORDS to
show matching the criteria. In that last instance, I need
the control object in the report to return a value of
ZERO. I can't do any math on the numbers as is without
averaging in the zero.

Thanks again!

-----Original Message-----

Hi. I posted a question a couple days ago about returning
a zero for a null value (fish captured in nets), and was
told to use the Nz() function. I obviously dont have the
syntax right. Can someone show me an example of code that
does this and where the code actually goes. I assume its
typed into the "Field" under the given column in the
Design View of a Query. I'm still confused about how to
use SQL or what "controls on a form" means so help pages
that I've seen going that route haven't helped me.

For what its worth, here's what I have come up with:

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT[,0]])

You have extra brackets in here. Should be...

ZEROCOUNT: Nz([GN Fishcount]![FISHCOUNT],0) + 0

Why the plus zero? Because unless you coerce it to a different type, Nz() will
return a string, and not just for the Null-To-Zero

values, but for all values in the

column. The "+ 0" tells Access to return a numeric

value
from the function.

.

.