Nz Expression

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

 

[Ken Snell \(MVP\)]

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)

 

[MGFoster]

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

 

[John Spencer]

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)

 

[Guest]

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

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)

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

 

[Ken Snell \(MVP\)]

INSERT INTO [Scalp]
SELECT [Hair]
FROM [Wastebasket];