Nz Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query base on a table and a query. One of the fields has cells that
are empty and I need a value in them. I've read alot of posts and information
on returning null values in a query and I'm sorry to say I still have a
question. I believe I can use the Nz expression. When I replace the field
heading with "Nz(Field3,[0])" it changes to "Expr1:Nz([Field3],[0])". When I
run the query it prompts me to enter the "Parameter Value". When I put "0" in
the box it enters a "0" value in all the cells that did not have an amount.
Thats the good new, I think!
What did I do wrong to get the prompt, is there another way or place in the
query to get the zero value without it? Is it because I used the Nz
expression in a query that it returned a zero string anyways? Is there a
better location to put the expression? As you can see I'm confused.
Any help anyone can give me with this will be appreciated. Thanks..
 
Don't put [ ] around the zero.

Nz(Field3, 0)

ACCESS adds the alias Expr1 automatically, and you can use your own alias if
you wish:

MyAliasName: Nz(Field3, 0)
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's a Access "help" that screws things up. Switch over to SQL view
and in the SELECT clause change

Nz([Field3],[0]) As Expr1

To

Nz(Field3,0) As Expr1

Access "helpfully" places brackets around some things it doesn't need to
place brackets around.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAi7KIechKqOuFEgEQIQFgCdHKQF3wksRVUc1mPu0sBtOFvyr44An1Q3
IpOopTgN1rYha/8XY9VSvISl
=NTos
-----END PGP SIGNATURE-----
 
You put [] around the 0 which tells the Access that this is the name of some
field. Since it can't find the field, it asks you for the value.

Try
Nz(Field3,0)
 
That did the trick. Thanks to everone who took the time to reply. I only have
one question left. How do I put my hair back in my scalp after pulling it out?
--
gumby


John Spencer said:
You put [] around the 0 which tells the Access that this is the name of some
field. Since it can't find the field, it asks you for the value.

Try
Nz(Field3,0)


gumby said:
I have a query base on a table and a query. One of the fields has cells
that
are empty and I need a value in them. I've read alot of posts and
information
on returning null values in a query and I'm sorry to say I still have a
question. I believe I can use the Nz expression. When I replace the field
heading with "Nz(Field3,[0])" it changes to "Expr1:Nz([Field3],[0])". When
I
run the query it prompts me to enter the "Parameter Value". When I put "0"
in
the box it enters a "0" value in all the cells that did not have an
amount.
Thats the good new, I think!
What did I do wrong to get the prompt, is there another way or place in
the
query to get the zero value without it? Is it because I used the Nz
expression in a query that it returned a zero string anyways? Is there a
better location to put the expression? As you can see I'm confused.
Any help anyone can give me with this will be appreciated. Thanks..
Click to expand...
Click to expand...
 
Back
Top