Nested Conditional Statements

[Guest]

Hello -

I have a column within a report that has several versions of a particular
entry. So I wrote a series of queries that would help to identify the
different versions, see below:

Version 0: IIf(InStr(Report![Column],"Business")>0,"Version 0","")

Version 1: IIf(InStr(Report![Column],".1")>0,"Version 1","")

Version 2: IIf(InStr([Report]![Column],".2")>0,"Version 2","")

Version 3: IIf(InStr(Report![Column],".3")>0,"Version 3","")

Version 4: IIf(InStr(Report![Column],".4")>0,"Version 4","")

Version Combine: [Version 0] & [Version 1] & [Version 2] & [Version 3] &
[Version 4]

Version: IIf(([Version Combine]=""),"Version 0",[Version Combine])


It works....kind of. I'm getting one entry that has two versions
'Version1Version2'. Looking at the entry within the column, it's clear that
Access would interpret the entry as both Version 1 and Version 2.

To prevent this problem, I'm trying to write a nested query. If column
"Version 0" is blank, then run Version 1 query. If column "Version 0" and
"Version 1" are blank, then run Version 2 query....and so on...

Please help.

Super thanks!

 

[Guest]

Whilst I hate the IIF syntax, it is
IIF(condition, true action, false action)
The true or false condition can be another IIF statement so you can code
nested conditions e.g.
IIF(condition,IIF(nested condition,true action,false action),false action)

-Dorian

 

[Michel Walsh]

You can also use a SWITCH :


SWITCH(
InStr(Report![Column],"Business")>0,"Version 0" ,
InStr(Report![Column],".1">0,"Version 1",
InStr([Report]![Column],".2")>0,"Version 2",
InStr(Report![Column],".3")>0,"Version 3",
InStr(Report![Column],".4")>0,"Version 4",
true, "Unknown" )



The arguments work in pair. The Switch returns the second member of the
first pair for which the first member of the pair is true.



Hoping it may help,
Vanderghast, Access MVP