Replace null value with zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Reynolds Inc - 28 Mar 2007 01:55 GMT

I am building queries off of other queries in Access. The table that one of
the first queries is retrieving info from has no data for this particular
query. Therefore when I run a query off the first query, it is returning a
null value in a cell that will then be queried (along with others) and
added up for a total value. So when the null value is added to the other
values, it returns a null value.

What I would like to do is have a process in place that will tell Access to
substitute the null value with a number zero. Is there a way to do this?

Thanks,

Dane

Reply to this Message

John Mishefske - 28 Mar 2007 05:54 GMT

> I am building queries off of other queries in Access. The table that one of
> the first queries is retrieving info from has no data for this particular
[quoted text clipped - 5 lines]
> What I would like to do is have a process in place that will tell Access to
> substitute the null value with a number zero. Is there a way to do this?

You could test the value with the IsNull() function within the IIf() function.

Qty: IIf(IsNull([YourCountField], 0,[YourCountField])

in SQL it would look like this:

SELECT IIf(IsNull([YourCountField], 0,[YourCountField]) As Qty ...

Signature

'---------------
'John Mishefske
'---------------

Reply to this Message

John W. Vinson - 28 Mar 2007 06:31 GMT

>What I would like to do is have a process in place that will tell Access to
>substitute the null value with a number zero. Is there a way to do this?

NZ([fieldname])

John W. Vinson [MVP]

Reply to this Message

Reynolds Inc - 28 Mar 2007 18:52 GMT

Thanks to John Mishefske and John W. Vinson for your help!

> I am building queries off of other queries in Access. The table that one of
> the first queries is retrieving info from has no data for this particular
[quoted text clipped - 9 lines]
>
> Dane

Reply to this Message

Briciola - 03 May 2007 16:37 GMT

>I am building queries off of other queries in Access. The table that one of
> the first queries is retrieving info from has no data for this particular
[quoted text clipped - 10 lines]
>
> Dane

nz([Field-name];0)

Reply to this Message