Nested Conditional Statement Using Nulls

[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...

I don't know how to write "if column 'version 0' is blank"...

Please help.

Super thanks!

 

[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.



You can also use multiple iif inside iif:

iif( condition1, value1, iif(condition2, value2, ... ) )


but it is somehow more painful to maintain.

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hello -

Thanks, but I've tried inputing the code and I keep getting a "wrong number
of arguments" error.

I'm not sure what to do.

Any help would greatly be appreciated.

Thanks for your time.

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.



You can also use multiple iif inside iif:

iif( condition1, value1, iif(condition2, value2, ... ) )


but it is somehow more painful to maintain.

Hoping it may help,
Vanderghast, Access MVP

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...

I don't know how to write "if column 'version 0' is blank"...

Please help.

Super thanks!

 

[Michel Walsh]

Missing a parenthesis too... Right after the ".1", should add a ), as it
is done for the other lines.


Vanderghast, Access MVP

Hello -

Thanks, but I've tried inputing the code and I keep getting a "wrong
number
of arguments" error.

I'm not sure what to do.

Any help would greatly be appreciated.

Thanks for your time.

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.



You can also use multiple iif inside iif:

iif( condition1, value1, iif(condition2, value2, ... ) )


but it is somehow more painful to maintain.

Hoping it may help,
Vanderghast, Access MVP

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...

I don't know how to write "if column 'version 0' is blank"...

Please help.

Super thanks!

 

[Guest]

Ah, thank you so much! It worked.

Missing a parenthesis too... Right after the ".1", should add a ), as it
is done for the other lines.


Vanderghast, Access MVP

Hello -

Thanks, but I've tried inputing the code and I keep getting a "wrong
number
of arguments" error.

I'm not sure what to do.

Any help would greatly be appreciated.

Thanks for your time.

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.



You can also use multiple iif inside iif:

iif( condition1, value1, iif(condition2, value2, ... ) )


but it is somehow more painful to maintain.

Hoping it may help,
Vanderghast, Access MVP



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...

I don't know how to write "if column 'version 0' is blank"...

Please help.

Super thanks!