If null --> place 0

  • Thread starter Thread starter Tommy
  • Start date Start date
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!!
 
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.
 
Use the nz function. The syntax is like - nz([YourField]),0). This will
replace nulls
with a zero.
 
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!!
Click to expand...
Click to expand...
 
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!!
Click to expand...
Click to expand...
 
Back
Top