Nested Conditional Statement Using Nulls

D

DyingIsis

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!
 
M

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
 
D

DyingIsis

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.

Michel Walsh said:
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



DyingIsis said:
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!
Click to expand...
Click to expand...
 
M

Michel Walsh

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


Vanderghast, Access MVP





DyingIsis said:
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.

Michel Walsh said:
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



DyingIsis said:
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!
Click to expand...
Click to expand...
Click to expand...
 
D

DyingIsis

Ah, thank you so much! It worked.

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


Vanderghast, Access MVP





DyingIsis said:
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.

Michel Walsh said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested Conditional Statements 2
VBA Coding Help for Beginner 0
Need some PWA help 0
Remove Identical words 0
Insert Index into a Table 1
Page numbering with Merged Document and IF statements 1
type mismatch error in macro in excel 0
Running Balance (Debit and Credit) in Access 2007 3

Top