conditional iif statement

  • Thread starter Thread starter Steve G
  • Start date Start date
S

Steve G

I need to display data in a query provided certain conditions exist. I've
begun an iif statement, however I am not certain how to embed the statements
properly.

Description: IIf([PatientStatus] = "Approved", "") does what I want for the
"approved" value. However, if patientStatus = pending, then I need to show
the reason it is pending, for instance,

Description: IIf([PatientStatus] = "Pending", "([reasonforpending]") and if
patientStatus=discharged then:

Description: IIf([PatientStatus] = "Discharged", "([reasonforDischarge])")

Any help in combining these statements into one statement would sure be
appreciated.

Steve G
 
You can nest IIf's.

=IIf([PatientStatus] = "Approved", "",IIf([PatientStatus] = "Pending",
[reasonforpending], IIf([PatientStatus] = "Discharged",
[reasonforDischarge], "None of the above")))

Actually, if you don't need anything shown if [PatientStatus] = "Approved"
and there are no other IIf's, just make it the False argument:

=IIf([PatientStatus] = "Pending", [reasonforpending], IIf([PatientStatus] =
"Discharged", [reasonforDischarge], ""))
 
Work from the outside in.
In general, the syntax is:
IIF(Condition, value_True, value_False)
so, let your various conditions be C1, C2, ...
and the corresponding values be V1, V2, ...
Start with:
IIf(C1, V1,
The next part is the value in case C1 is False. In your case, this is
not a single answer, it could be V2 or V3 or ...
So, let the "False" part above start with:
IIf(C2, V2,
This 2nd level IIf is still not complete because the value if it is
False (which means that C1 was also false) could still be V3, V4, etc.
Eventually, you will get to some point where this stops -- let DEFAULT
be the value if None of the conditions are met. You wind up with
something like this:

IIf(C1, V1, (IIf(C2, V2, ( ... IIf(Cn, Vn, DEFAULT) ) )

Work it out with only 2 cases. Then do 3. Use parentheses to keep
track.
It gets messy REALLY FAST, but it is all straightforward. Patience
helps.

I also suggest that you type this up in some other app and then paste it
into the query editor (go straight to the SQL view if you wish). The
Access editor is AWFUL for this -- the font is terrible and parentheses
become very hard to count.

Note: If this were in VB code, you would use a Select Case. I don't
think there is a function type counterpart to this though ...
-=-=-=
 
Thank you Fred, I appreciate the help.

Steve G

Fredg said:
You can nest IIf's.

=IIf([PatientStatus] = "Approved", "",IIf([PatientStatus] = "Pending",
[reasonforpending], IIf([PatientStatus] = "Discharged",
[reasonforDischarge], "None of the above")))

Actually, if you don't need anything shown if [PatientStatus] = "Approved"
and there are no other IIf's, just make it the False argument:

=IIf([PatientStatus] = "Pending", [reasonforpending], IIf([PatientStatus] =
"Discharged", [reasonforDischarge], ""))
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Steve G said:
I need to display data in a query provided certain conditions exist. I've
begun an iif statement, however I am not certain how to embed the statements
properly.

Description: IIf([PatientStatus] = "Approved", "") does what I want for the
"approved" value. However, if patientStatus = pending, then I need to show
the reason it is pending, for instance,

Description: IIf([PatientStatus] = "Pending", "([reasonforpending]") and if
patientStatus=discharged then:

Description: IIf([PatientStatus] = "Discharged", "([reasonforDischarge])")

Any help in combining these statements into one statement would sure be
appreciated.

Steve G
Click to expand...
Click to expand...
 
Thanks jfp:

I believe you are correct about the vb code stuff...thanks for the help.

Steve G

jfp said:
Work from the outside in.
In general, the syntax is:
IIF(Condition, value_True, value_False)
so, let your various conditions be C1, C2, ...
and the corresponding values be V1, V2, ...
Start with:
IIf(C1, V1,
The next part is the value in case C1 is False. In your case, this is
not a single answer, it could be V2 or V3 or ...
So, let the "False" part above start with:
IIf(C2, V2,
This 2nd level IIf is still not complete because the value if it is
False (which means that C1 was also false) could still be V3, V4, etc.
Eventually, you will get to some point where this stops -- let DEFAULT
be the value if None of the conditions are met. You wind up with
something like this:

IIf(C1, V1, (IIf(C2, V2, ( ... IIf(Cn, Vn, DEFAULT) ) )

Work it out with only 2 cases. Then do 3. Use parentheses to keep
track.
It gets messy REALLY FAST, but it is all straightforward. Patience
helps.

I also suggest that you type this up in some other app and then paste it
into the query editor (go straight to the SQL view if you wish). The
Access editor is AWFUL for this -- the font is terrible and parentheses
become very hard to count.

Note: If this were in VB code, you would use a Select Case. I don't
think there is a function type counterpart to this though ...
-=-=-=
Steve said:
I need to display data in a query provided certain conditions exist. I've
begun an iif statement, however I am not certain how to embed the statements
properly.

Description: IIf([PatientStatus] = "Approved", "") does what I want for the
"approved" value. However, if patientStatus = pending, then I need to show
the reason it is pending, for instance,

Description: IIf([PatientStatus] = "Pending", "([reasonforpending]") and if
patientStatus=discharged then:

Description: IIf([PatientStatus] = "Discharged", "([reasonforDischarge])")

Any help in combining these statements into one statement would sure be
appreciated.

Steve G
Click to expand...
Click to expand...
 
IF you did things differently and used integers instead of strings (as
in an Enum) then you could use the Choose() function in VB rather than
an ugly nesting of IIf()s.
-=-=-=
Steve said:
Thanks jfp:

I believe you are correct about the vb code stuff...thanks for the help.

Steve G

jfp said:
Work from the outside in.
In general, the syntax is:
IIF(Condition, value_True, value_False)
so, let your various conditions be C1, C2, ...
and the corresponding values be V1, V2, ...
Start with:
IIf(C1, V1,
The next part is the value in case C1 is False. In your case, this is
not a single answer, it could be V2 or V3 or ...
So, let the "False" part above start with:
IIf(C2, V2,
This 2nd level IIf is still not complete because the value if it is
False (which means that C1 was also false) could still be V3, V4, etc.
Eventually, you will get to some point where this stops -- let DEFAULT
be the value if None of the conditions are met. You wind up with
something like this:

IIf(C1, V1, (IIf(C2, V2, ( ... IIf(Cn, Vn, DEFAULT) ) )

Work it out with only 2 cases. Then do 3. Use parentheses to keep
track.
It gets messy REALLY FAST, but it is all straightforward. Patience
helps.

I also suggest that you type this up in some other app and then paste it
into the query editor (go straight to the SQL view if you wish). The
Access editor is AWFUL for this -- the font is terrible and parentheses
become very hard to count.

Note: If this were in VB code, you would use a Select Case. I don't
think there is a function type counterpart to this though ...
-=-=-=
Steve said:
I need to display data in a query provided certain conditions exist. I've
begun an iif statement, however I am not certain how to embed the statements
properly.

Description: IIf([PatientStatus] = "Approved", "") does what I want for the
"approved" value. However, if patientStatus = pending, then I need to show
the reason it is pending, for instance,

Description: IIf([PatientStatus] = "Pending", "([reasonforpending]") and if
patientStatus=discharged then:

Description: IIf([PatientStatus] = "Discharged", "([reasonforDischarge])")

Any help in combining these statements into one statement would sure be
appreciated.

Steve G
Click to expand...
Click to expand...
Click to expand...
 
Back
Top