IF isnull with and

[Number4]

This is a snippet from a nested if statement:  IF IsNull([5]) AND IsNull([6]) AND [1] = [2] = [3] = [4] then 1 else 2 endif results in a 2 when I'd expect a 1.

If I can get this to work, the full statement should, which just accounts for nulls in up to 5 columns.

Perhaps there's an easier way.  I want to know if all the cells that aren't Null contain the same data or value (in the example, it would be columns 1-4.)

Not all rows have data in all columns, but the Nulls are always to the right.

[BenMoss · Accepted solution]

The syntax as...

 [1]=[2] AND [2]=[3] AND [3]=[4] 

works how you would like @Number4 

[rarmstrong · Accepted solution]

Try

IF IsNull([5])

AND IsNull([6])

AND [1] = [2]

AND [2] = [3]

AND [3] = [4]

then 1 else 2 endif

[Number4]

Okay, so this fixes it:

IF Isnull([5]) AND isnull([6]) AND [1] = [2] AND [1] = [3] AND [1] = [4] then 1 else 2 endif

Is there a way to make [1] = [2] =[3] = [4] work instead?

[Number4]

Thanks @BenMoss and @rarmstrong.  You both came up with what I figured out as well, hadn't seen your posts before posting mine.

Is there a way to group them instead?

[BenMoss]

The way you are asking suggests your actual problem/data is a bit more complex than the example and thus this logic may be tiresome to implement.

I may advise something different if we understood the wider piece that you are trying to resolve.

But it does not seem [1]=[2]=[3] will work as a logical statement and return TRUE as you wish.

Ben