Nested IIf stops after two conditions

[FrankTimJr]

Can anyone help me figure out why this nested IIf statement is stopping after
the first two conditions? I have a total of 5 conditions I need to add to
it. Unless there is a 2 condition limit?

WtW Chg: IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",IIf([Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",IIf([Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")))

Thanks in advance.
Frank

 

[Clifford Bass]

Hi Frank,

What precisely do you mean by "stops"? An error? If so, what? If
not, are there any of those fields that might contain nulls? If so, you
should first check if a field is null before trying to compare it. Also, you
may want to consider the Switch() function instead:

Switch([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",[Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",[Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y",True,"N")

Or maybe just combine it all with Or:

IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K] Or [Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K] Or [Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")

Hope that helps,

Clifford Bass

 

[FrankTimJr]

What I mean by stops is that when [Sales Phase] is different between the two
tables, the result is returning "N" when it should be returning "Y".

What happens if [Previous FAS Snapshot].[Sales Phase] is null and [Current
FAS Snapshot].[Sales Phase] is not null? Does that impact the IIf statement?

I like the second IIf statement you wrote. I'll give that one a try.

Thanks,
Frank

Hi Frank,

What precisely do you mean by "stops"? An error? If so, what? If
not, are there any of those fields that might contain nulls? If so, you
should first check if a field is null before trying to compare it. Also, you
may want to consider the Switch() function instead:

Switch([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",[Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",[Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y",True,"N")

Or maybe just combine it all with Or:

IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K] Or [Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K] Or [Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")

Hope that helps,

Clifford Bass

Can anyone help me figure out why this nested IIf statement is stopping after
the first two conditions? I have a total of 5 conditions I need to add to
it. Unless there is a 2 condition limit?

WtW Chg: IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",IIf([Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",IIf([Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")))

Thanks in advance.
Frank

 

[Marshall Barton]

Can anyone help me figure out why this nested IIf statement is stopping after
the first two conditions? I have a total of 5 conditions I need to add to
it. Unless there is a 2 condition limit?

WtW Chg: IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",IIf([Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",IIf([Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")))

Using nested IIf functions gets very confusing very quickly
so you should avoid them if there's another way.

In this case, it looks like you want a Y for any of the
matching conditions and N when none of them match. One
other way to do that could be:

WtW Chg: IIf([Previous FAS Snapshot].[Proposed Monthly
$K]<>[Current FAS Snapshot].[Proposed Monthly $K] OR
[Previous FAS Snapshot].[Proposed TCV $K]<>[Current FAS
Snapshot].[Proposed TCV $K] OR [Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales
Phase] OR . . . ,"Y","N")

 

[FrankTimJr]

Clifford,

I used your IIf statement with the "OR" in it and I also made sure my
example records had values in both tables. It works perfectly.

If I need to check for Null values first, how do I accomplish this? All of
the fields I need to check SHOULD have values in them but in the event there
isn't I might need to tag those as a "Y" as well.

Hi Frank,

What precisely do you mean by "stops"? An error? If so, what? If
not, are there any of those fields that might contain nulls? If so, you
should first check if a field is null before trying to compare it. Also, you
may want to consider the Switch() function instead:

Switch([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",[Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",[Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y",True,"N")

Or maybe just combine it all with Or:

IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K] Or [Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K] Or [Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")

Hope that helps,

Clifford Bass

Can anyone help me figure out why this nested IIf statement is stopping after
the first two conditions? I have a total of 5 conditions I need to add to
it. Unless there is a 2 condition limit?

WtW Chg: IIf([Previous FAS Snapshot].[Proposed Monthly $K]<>[Current FAS
Snapshot].[Proposed Monthly $K],"Y",IIf([Previous FAS Snapshot].[Proposed TCV
$K]<>[Current FAS Snapshot].[Proposed TCV $K],"Y",IIf([Previous FAS
Snapshot].[Sales Phase]<>[Current FAS Snapshot].[Sales Phase],"Y","N")))

Thanks in advance.
Frank

 

[Clifford Bass]

Hi Frank,

By different, if those are text, is the difference more than just the
case of the text? Access considers "abc" and "ABC" to be equal. If one is
null and the other is not null then the comparison will never be True. Null
means the value is unknown; as opposed to there is no value. It is a
somewhat subtle distinction. An example I like is the Address1, Address2,
Address3 situation. If the address is "123 Main St." then you leave Address2
and Address3 blank because there is no second or third line. But this often
results in Address2 and Address3 being stored in a database as null, which
technically means the second and third lines are not known. Which is not
true because they are known. They are known to be blank. Technically in
that case they should be stored as zero-length strings.

Anyway, when you compare an unknown (null) to a known, there is no way
to know if that unknown value is or is not the same as the known value. So
this should return a null as a result. Which means that the results of the
comparison are not known. But IIf() only provides for a True and a False
value. So with the result of the comparison not being known, it is probably
better to assume False and return the value from that part. (If anyone wants
to disagree with the above because my understanding is wrong, please do so.)

Given all of that, what you may want to do is something like this which
changes nulls into zero-length strings which can indeed be compared:

IIf( ... IIf(IsNull([Previous FAS Snapshot].[Sales Phase]), "",
[Previous FAS Snapshot].[Sales Phase]) <> IIf(IsNull([Current FAS
Snapshot].[Sales Phase]), "", [Current FAS Snapshot].[Sales Phase]), "Y", "N")

Or, if Sales Phase is numeric, you might change the nulls to some value
that will not occur with the real data (maybe -1):

IIf( ... IIf(IsNull([Previous FAS Snapshot].[Sales Phase]), -1,
[Previous FAS Snapshot].[Sales Phase]) <> IIf(IsNull([Current FAS
Snapshot].[Sales Phase]), -1, [Current FAS Snapshot].[Sales Phase]), "Y", "N")

Note that you can use the Nz() function instead of the IIf(IsNull()...,
, ) construct, but that function is not built into the Jet Engine and
therefore requires the query tool to call out to VBA. Which can impact
performance. It also means that if you have to execute the query from
outside of Access for some reason, it will not work with the Nz() function,
but will with the IIf(IsNull()..., , ) construct.

Hope that helps,

Clifford Bass

What I mean by stops is that when [Sales Phase] is different between the two
tables, the result is returning "N" when it should be returning "Y".

What happens if [Previous FAS Snapshot].[Sales Phase] is null and [Current
FAS Snapshot].[Sales Phase] is not null? Does that impact the IIf statement?

I like the second IIf statement you wrote. I'll give that one a try.

Thanks,
Frank

 

[Clifford Bass]

Hi Frank,

See my other (lengthy) reply.

Clifford Bass

 

[FrankTimJr]

Excellent! I had no idea that Null means an unknown value. I had always
thought it meant blank.

Thank you for all your help!

Hi Frank,

By different, if those are text, is the difference more than just the
case of the text? Access considers "abc" and "ABC" to be equal. If one is
null and the other is not null then the comparison will never be True. Null
means the value is unknown; as opposed to there is no value. It is a
somewhat subtle distinction. An example I like is the Address1, Address2,
Address3 situation. If the address is "123 Main St." then you leave Address2
and Address3 blank because there is no second or third line. But this often
results in Address2 and Address3 being stored in a database as null, which
technically means the second and third lines are not known. Which is not
true because they are known. They are known to be blank. Technically in
that case they should be stored as zero-length strings.

Anyway, when you compare an unknown (null) to a known, there is no way
to know if that unknown value is or is not the same as the known value. So
this should return a null as a result. Which means that the results of the
comparison are not known. But IIf() only provides for a True and a False
value. So with the result of the comparison not being known, it is probably
better to assume False and return the value from that part. (If anyone wants
to disagree with the above because my understanding is wrong, please do so.)

Given all of that, what you may want to do is something like this which
changes nulls into zero-length strings which can indeed be compared:

IIf( ... IIf(IsNull([Previous FAS Snapshot].[Sales Phase]), "",
[Previous FAS Snapshot].[Sales Phase]) <> IIf(IsNull([Current FAS
Snapshot].[Sales Phase]), "", [Current FAS Snapshot].[Sales Phase]), "Y", "N")

Or, if Sales Phase is numeric, you might change the nulls to some value
that will not occur with the real data (maybe -1):

IIf( ... IIf(IsNull([Previous FAS Snapshot].[Sales Phase]), -1,
[Previous FAS Snapshot].[Sales Phase]) <> IIf(IsNull([Current FAS
Snapshot].[Sales Phase]), -1, [Current FAS Snapshot].[Sales Phase]), "Y", "N")

Note that you can use the Nz() function instead of the IIf(IsNull()...,
, ) construct, but that function is not built into the Jet Engine and
therefore requires the query tool to call out to VBA. Which can impact
performance. It also means that if you have to execute the query from
outside of Access for some reason, it will not work with the Nz() function,
but will with the IIf(IsNull()..., , ) construct.

Hope that helps,

Clifford Bass

What I mean by stops is that when [Sales Phase] is different between the two
tables, the result is returning "N" when it should be returning "Y".

What happens if [Previous FAS Snapshot].[Sales Phase] is null and [Current
FAS Snapshot].[Sales Phase] is not null? Does that impact the IIf statement?

I like the second IIf statement you wrote. I'll give that one a try.

Thanks,
Frank

 

[Clifford Bass]

Hi Frank,

It is understandable that you would not necessarily know the
distinction. It is often mushed around by database products, including
Access. Although less so by Access than by some others.

You are quite welcome!

Clifford Bass