Use of Nz functions

  • Thread starter Thread starter Handy Jane
  • Start date Start date
H

Handy Jane

Hi Fellas,

I am fairly new to Access and am tring to write a query
that will change my null values to zeros. I have been
told to use the Nz function, but dont know where to put it
or how to use it.

Please help with step by step

Thanks

HJ
 
To replace all Nulls in a field with a zero:
1. Create a query into this table.

2. Drag the field to be changed into the query grid.

3. In the Criteria row under this field, enter:
Is Null

4. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid.

5. In the Update row under this field, enter:
0

6. Run the query.

Before you do that, the question arises as to whether this is a good idea,
or why you may need to do it. You might like to read:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html
 
Handy Jane said:
Hi Fellas,

I am fairly new to Access and am tring to write a query
that will change my null values to zeros. I have been
told to use the Nz function, but dont know where to put it
or how to use it.

Please help with step by step

Thanks

HJ
Click to expand...
If you have a field in you qry like [Price] you can write it like this
(Nz([Price])) and it will show 0 if the field is empty.

Sigurd
 
Hi Sigurd,

we have tried this, but it then turns it into an
expression: Expr3: Sum((Nz([Value]))) which does not
allow the query to run.

It gives the error:
Compile error in query expression.

I'm probably being a bit stupid, please help
-----Original Message-----

"Handy Jane" <[email protected]> skrev i melding
Hi Fellas,

I am fairly new to Access and am tring to write a query
that will change my null values to zeros. I have been
told to use the Nz function, but dont know where to put it
or how to use it.

Please help with step by step

Thanks

HJ
Click to expand...
If you have a field in you qry like [Price] you can write it like this
(Nz([Price])) and it will show 0 if the field is empty.

Sigurd


.
Click to expand...
 
Handy Jane said:
Hi Sigurd,

we have tried this, but it then turns it into an
expression: Expr3: Sum((Nz([Value]))) which does not
allow the query to run.

It gives the error:
Compile error in query expression.
Click to expand...

Hi Handy Jane

Yes, the qyery create an alias like Expr3. You can name the alias yourselves
like " NewValue:((Nz([Value]))) "

Try to run it again like this Expr3:(Nz([Value]))
In the totals field you select or write sum
This should work!

Sigurd
 
You can try the expression like this:

New value: Nz([fieldname],0)

Eechhutti R.Rajasekaran


Sigurd Bruteig said:
Handy Jane said:
Hi Sigurd,

we have tried this, but it then turns it into an
expression: Expr3: Sum((Nz([Value]))) which does not
allow the query to run.

It gives the error:
Compile error in query expression.
Click to expand...

Hi Handy Jane

Yes, the qyery create an alias like Expr3. You can name the alias yourselves
like " NewValue:((Nz([Value]))) "

Try to run it again like this Expr3:(Nz([Value]))
In the totals field you select or write sum
This should work!

Sigurd
Click to expand...
 
Thanks for your efforts but I am still getting a compiler
error, If you could send me a possible screen dump to
(e-mail address removed)

Thanks

HJ
-----Original Message-----
You can try the expression like this:

New value: Nz([fieldname],0)

Eechhutti R.Rajasekaran


Sigurd Bruteig said:
"Handy Jane" <[email protected]> skrev i melding
Hi Sigurd,

we have tried this, but it then turns it into an
expression: Expr3: Sum((Nz([Value]))) which does not
allow the query to run.

It gives the error:
Compile error in query expression.
Click to expand...

Hi Handy Jane

Yes, the qyery create an alias like Expr3. You can name
Click to expand...
the alias
yourselves
like " NewValue:((Nz([Value]))) "

Try to run it again like this Expr3:(Nz([Value]))
In the totals field you select or write sum
This should work!

Sigurd
Click to expand...


.
Click to expand...
 
Perhaps your references are messed up.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

Open any code module and select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Handy Jane said:
Thanks for your efforts but I am still getting a compiler
error, If you could send me a possible screen dump to
(e-mail address removed)

Thanks

HJ
-----Original Message-----
You can try the expression like this:

New value: Nz([fieldname],0)

Eechhutti R.Rajasekaran


Sigurd Bruteig said:
"Handy Jane" <[email protected]> skrev i melding
Hi Sigurd,

we have tried this, but it then turns it into an
expression: Expr3: Sum((Nz([Value]))) which does not
allow the query to run.

It gives the error:
Compile error in query expression.


Hi Handy Jane

Yes, the qyery create an alias like Expr3. You can name
Click to expand...
the alias
yourselves
like " NewValue:((Nz([Value]))) "

Try to run it again like this Expr3:(Nz([Value]))
In the totals field you select or write sum
This should work!

Sigurd
Click to expand...


.
Click to expand...
Click to expand...
 
Handy Jane said:
Hi Fellas,

I am fairly new to Access and am tring to write a query
that will change my null values to zeros. I have been
told to use the Nz function, but dont know where to put it
or how to use it.

Please help with step by step

Thanks

HJ
Click to expand...

The NZ function is not supported before Access 2000 I think. What version of
Access are you running?

sigurd
 
One, NZ is supported in Access 97.

Can you post the SQL statement that is not working. Also, post the exact error.

One method (generic sql - replace with your own table and field names).

To update the field contents:

UPDATE [TableName]
SET [FieldName] = 0
WHERE [FieldName] is Null

This will replace all null values with Zero. If you just want to get the Sum of
the some field, you don't need to use NZ since Sum ignores nulls.

Field: MyTotal: Sum([Value])

If you want to show Zero where the sum is Null, you might need

Field: MyTotal: NZ(Sum([Value]),0)

Also, if Value (Not a wise choice for a fieldname) is not a number field, but a
text field this will probably blow up. If it is a text field with only numbers,
you coud use the Val function.

Field: MyTotal: Sum(Val([Value]))

In other words, there are a lot of different scenarios that could be causing
your problem. For instance, if you are using the calculated field elsewhere the
use of NZ will turn it into a string, so you may need to force it back into a
number using one of the conversion functions (Val, CDbl, CCur, ...)

Hope this helps
 
Sigurd said:
The NZ function is not supported before Access 2000 I think. What version of
Access are you running?
Click to expand...

It *is* supported in Access 97 (and I'm pretty sure it was in 95 but who
runs that?)
 
Back
Top