StAbDh
asked on
Trouble converting null to 0 in Access 2007
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?
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?
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Nz( [datafield], 0 )
or
IIf( IsNull( [datafield] ), 0, [datafield] )
--
Graham