Multiple iif challenge

[S Himmelrich]

I know the syntax of iif within MS Access Queries. However I can you
build an iif statement that has the option to choice one of three
values to populate result, as opposted to one of two values to
populate resulting field?

This is the statement I'm working on, which works:


PMM Methodolgy: iif([Project Type]="Enhancement-Large" Or [Project
Type]="New-Large" Or [Project Type]="DTSS-CALFLA-Discovery", "PMM
Workflow", "Fast Track Workflow")

I need to evaluate the "Project Type" field and if it's null or ""
then the value should be "No Project Type" - how do I integrate this
logic into my currently working iif statement?

Thanks!

 

[John W. Vinson]

I know the syntax of iif within MS Access Queries. However I can you
build an iif statement that has the option to choice one of three
values to populate result, as opposted to one of two values to
populate resulting field?

This is the statement I'm working on, which works:


PMM Methodolgy: iif([Project Type]="Enhancement-Large" Or [Project
Type]="New-Large" Or [Project Type]="DTSS-CALFLA-Discovery", "PMM
Workflow", "Fast Track Workflow")

I need to evaluate the "Project Type" field and if it's null or ""
then the value should be "No Project Type" - how do I integrate this
logic into my currently working iif statement?

Thanks!

Whenever you have three or more branches, you can use either nested IIF calls,
or - often better - the Switch() function. Switch() takes any number of
arguments in pairs; it evaluates the pairs left to right, and when the first
element of a pair is TRUE it returns the second element of that pair and
quits. So you could use

PMM Methodology: Switch([Project Type]="Enhancement-Large" OR [Project
Type]="New-Large" Or [Project Type]="DTSS-CALFLA-Discovery", "PMM Workflow",
[Project Type] IS NULL, "No Project Type", True, "Fast Track Workflow")

If [Project Type] could contain either a Zero Length String or a NULL you can
use

Len([Project Type] & "") = 0

instead of [Project Type] IS NULL in the expression.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

Thank you John - I ended up using the Len function instead to replace
the NULL, it works great - thank for showing me the use of the Switch
function.
Regards,
Scott H.

I know the syntax of iif within MS Access Queries.  However I can you
build an iif statement that has the option to choice one of three
values to populate result, as opposted to one of two values to
populate resulting field?

This is the statement I'm working on, which works:

PMM Methodolgy: iif([Project Type]="Enhancement-Large" Or [Project
Type]="New-Large" Or [Project Type]="DTSS-CALFLA-Discovery", "PMM
Workflow", "Fast Track Workflow")

I need to evaluate the "Project Type" field and if it's null or ""
then the value should be "No Project Type" - how do I integrate this
logic into my currently working iif statement?

Thanks!

Whenever you have three or more branches, you can use either nested IIF calls,
or - often better - the Switch() function. Switch() takes any number of
arguments in pairs; it evaluates the pairs left to right, and when the first
element of a pair is TRUE it returns the second element of that pair and
quits. So you could use

PMM Methodology: Switch([Project Type]="Enhancement-Large" OR [Project
Type]="New-Large" Or [Project Type]="DTSS-CALFLA-Discovery", "PMM Workflow",
[Project Type] IS NULL, "No Project Type", True, "Fast Track Workflow")

If [Project Type] could contain either a Zero Length String or a NULL youcan
use

Len([Project Type] & "") = 0

instead of [Project Type] IS NULL in the expression.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -