how do i change null values to zero in an access query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thedean_kevin - 14 Nov 2005 21:34 GMT

ok here's the deal i have a query that works with linked tables and resident
tables
basically it's a query that returns money balances from five different
companies within an organization to corresponding names from
another/permanent table (the customer lists). The problem is the customer
list contains names that the other tables do not have so i'm left with a lot
of null values. I've also designed a form to calculate the total for these
five companies, however, the total balance does not work unless all the
tables has a value in it. Hence I need to convert the null values to zeros so
that my total balance for all five balances or companies to work.

Please help me, i'm kinna desperate

Reply to this Message

Brendan Reynolds - 14 Nov 2005 21:44 GMT

If you will use this query only within Microsoft Access, you can use the
Nz() function, for example ...

SELECT Nz([SomeField], 0) AS Whatever FROM SomeTable

However, this will only work in Microsoft Access. If you think you may need
to use the query from some other program, you can use the IIf() function
instead ...

SELECT IIf([SomeField] IS NULL, 0, [SomeField]) AS Whatever FROM SomeTable

See NZ Function and IIf Function in the help file for details.

Signature

Brendan Reynolds

> ok here's the deal i have a query that works with linked tables and
> resident
[quoted text clipped - 11 lines]
>
> Please help me, i'm kinna desperate

Reply to this Message