If null --> place 0

T

Tommy

Hi All

I have a silly question that when i make a query, sometimes a field may not
come up anything (null). How can a put a formula on it so that it will
display 0 insteal of null.

Thank you!!
 
A

akphidelt

You can't do that within the query unless

1) You do an update query... you use 0 as the update to value and Null as
the value you want to update

2) You can create another column in the query, that says something like
=IIF(IsNull([YourColumn]),0,[Yourcolumn])

Or you can do secret option number 3 and go in to the table you use for the
query and change the default value of that field to 0. In order to get this
information to the query regarding previous data you will have to use option
number 1 first. But option #3 will prevent this from happenin in the future.
 
B

Beetle

Use the nz function. The syntax is like - nz([YourField]),0). This will
replace nulls
with a zero.
 
T

Tommy

it works.
Thank you very much!!!!

Beetle said:
Use the nz function. The syntax is like - nz([YourField]),0). This will
replace nulls
with a zero.
--
_________

Sean Bailey


Tommy said:
Hi All

I have a silly question that when i make a query, sometimes a field may
not
come up anything (null). How can a put a formula on it so that it will
display 0 insteal of null.

Thank you!!
 
J

Jeff Boyce

Changing the default value for a field in a table won't change values in
already existing records.

It isn't necessary to do an update query to display a 0 instead of a Null --
see Access HELP on the Nz() function.

Your option #2 would work, but it may be unnecessary to create an extra
column in the query (see previous).

Regards

Jeff Boyce
Microsoft Office/Access MVP

akphidelt said:
You can't do that within the query unless

1) You do an update query... you use 0 as the update to value and Null as
the value you want to update

2) You can create another column in the query, that says something like
=IIF(IsNull([YourColumn]),0,[Yourcolumn])

Or you can do secret option number 3 and go in to the table you use for
the
query and change the default value of that field to 0. In order to get
this
information to the query regarding previous data you will have to use
option
number 1 first. But option #3 will prevent this from happenin in the
future.

Tommy said:
Hi All

I have a silly question that when i make a query, sometimes a field may
not
come up anything (null). How can a put a formula on it so that it will
display 0 insteal of null.

Thank you!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top