Dealing with ' 0 ' values and NULL values in a query calculation

[Guest]

I am working with 2 columns of data and I am taking the % difference between
them => Column A and Column B. If Column A = ' 0 ' or is NULL, then I want
the % calculation to return a value of ' 1 '. But I cannot get the SQL
right. Here is what I have:

IIF([TABLE_X].[columnA] is not null OR [TABLE_X].[columnA] <> '0'
,(([TABLE_Y].[columnB])-([TABLE_X].[columnA]))/([TABLE_X].[columnA]),1) AS
Perc_Chg

So what I am telling Access to do (but it is not listening to me) is: 'If
Column A is not a null value or it is not equal to 0, then perform the
percentage change calculation. If it is null or = 0, then return a value of
1.'

 

[Bill]

you want AND rather than OR. Alternatively,
IIF(nulltozero(ColumnA)=0,1,ColumnB-ColumnA)

where nulltozero is not built in, but it's a handy function and you can get
it from the ol' Northwind example if you need to.

 

[Guest]

I changed it to AND and it told me that it set all values to NULL due to a
conversion type failure...

you want AND rather than OR. Alternatively,
IIF(nulltozero(ColumnA)=0,1,ColumnB-ColumnA)

where nulltozero is not built in, but it's a handy function and you can get
it from the ol' Northwind example if you need to.

I am working with 2 columns of data and I am taking the % difference
between
them => Column A and Column B. If Column A = ' 0 ' or is NULL, then I
want
the % calculation to return a value of ' 1 '. But I cannot get the SQL
right. Here is what I have:

IIF([TABLE_X].[columnA] is not null OR [TABLE_X].[columnA] <> '0'
,(([TABLE_Y].[columnB])-([TABLE_X].[columnA]))/([TABLE_X].[columnA]),1) AS
Perc_Chg

So what I am telling Access to do (but it is not listening to me) is: 'If
Column A is not a null value or it is not equal to 0, then perform the
percentage change calculation. If it is null or = 0, then return a value
of
1.'

 

[Guest]

I just changed my SQL and used your recommendation of

'IIF((ColumnA) is NULL OR (ColumnA)=0,1,ColumnB-ColumnA)'

and it worked beautifully.

Thanks for your help...

you want AND rather than OR. Alternatively,
IIF(nulltozero(ColumnA)=0,1,ColumnB-ColumnA)

where nulltozero is not built in, but it's a handy function and you can get
it from the ol' Northwind example if you need to.

I am working with 2 columns of data and I am taking the % difference
between
them => Column A and Column B. If Column A = ' 0 ' or is NULL, then I
want
the % calculation to return a value of ' 1 '. But I cannot get the SQL
right. Here is what I have:

IIF([TABLE_X].[columnA] is not null OR [TABLE_X].[columnA] <> '0'
,(([TABLE_Y].[columnB])-([TABLE_X].[columnA]))/([TABLE_X].[columnA]),1) AS
Perc_Chg

So what I am telling Access to do (but it is not listening to me) is: 'If
Column A is not a null value or it is not equal to 0, then perform the
percentage change calculation. If it is null or = 0, then return a value
of
1.'