Null & 0 in query

Topic

New Reply

Hi

I can’t find my notes for the syntax in a query.

If a field is null or 0 I need to change it to “UNKNOWN”

Is it an IIF statement ? ages since I messed about with Access.

Reply | Quote

Replies

The folowing will return UNKNOWN if the field is either 0 or NULL

iif(isnull([field])or[field]=0,”UNKNOWN”,[field])

Reply | Quote

Thaks for the help but I am getting “Circular Reference caused by Alias… ”

RepairCosts: IIf(IsNull([RepairCosts]) Or [RepairCosts]=0,”UNKNOWN”,[RepairCosts])

Reply | Quote

If you want to give the column the same name as a field used in the expression, you must specify the table or query name for the field:

RepairCosts: IIf(IsNull([NameOfTable].[RepairCosts]) Or [NameOfTable].[RepairCosts]=0,”UNKNOWN”,[NameOfTable].[RepairCosts])

Reply | Quote

Cheers guy’s

Works perfect.

Reply | Quote