Trouble converting null to 0 in Access 2007

StAbDh
StAbDh used Ask the Experts™
on
Hello

I am having trouble trying to simply convert null values to 0.

I am running a query based on a table.  One of the table data fields has no values.  When I select this field for the Query, I want to replace the null values with 0 using an expression.

I have tried the following (and other variations)with no luck:

iif(nz([datafield]),0)
iif(isnull([datafield]),0)

Any thoughts?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Use either:
    Nz( [datafield], 0 )
or
    IIf( IsNull( [datafield] ), 0, [datafield] )
--
Graham

Author

Commented:
I appreciate your help.  Unfortunately, after trying both suggestions, when I view the query there is still nothing in this data column.  Could something else be going on here?
Is this standard Access?
Either of those should work, but maybe you could try this:
IIf( [datafield] is Null, 0, [datafield] )
--
Graham

Author

Commented:
I went back to the table and the data type for this field was set as text.  After changing the type to number you suggestions worked.  Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial