IIIF statement with multiple conditions

[Drew77]

Help... back using Access after a long break. Trying to use an IIF statement but forgotten how to construct.

So what I am trying to so is as below:-

If Code = 123 and Description = ABC then true else false, If Code = 456 and Description = DEF then true else false ..... and adding further conditions.

Anyone help or point me to resources/similar thread.

Thanks,

Drew77

 

[ranman256]

When you start having this many options, put them in a lookup table.

, [desc], [value]
123,  ABC, true
[COLOR=#333333]456 [/COLOR],  [COLOR=#333333]DEF [/COLOR], true

join your main table to this to get your desired result.  No code.  Just query.

 

[Drew77]

Thanks ranman will give it a go

 

[mathematician]

Help... back using Access after a long break. Trying to use an IIF statement but forgotten how to construct.

So what I am trying to so is as below:-

If Code = 123 and Description = ABC then true else false, If Code = 456 and Description = DEF then true else false ..... and adding further conditions.

Anyone help or point me to resources/similar thread.

Thanks,

Drew77

Use switch instead of IIF for multiple ifs

=SWITCH(AND(code=123,Description="ABC"),TRUE,AND(code=456,Description="DEF"),TRUE,...)

 

[james_lankford]

^ isn't that an excel thing ?

 

[mathematician]

^ isn't that an excel thing ?

Search for help on SWITCH in access for more.

 

[james_lankford]

Search for help on SWITCH in access for more.

I was meaning the way AND is written as a function

 

[Micron]

Drew77: some key considerations.
- Each test must return a Boolean or a number that can be interpreted as a Boolean.
- If any test causes an error, the whole function results in an error.
- If no tests result in True, the function returns Null, so you may need to wrap the whole thing in the NZ function
- AFAIK, the And function in Access cannot be applied as suggested by mathemetician. It has to be declared for each test: IF this And this And this... Then
- To nest And within Switch: Nz(Switch(test1 = "orange" And test2 = "aardvark", "juicy", test3 = "grapefruit" And test4 = "whale", "mammal"), "No Switch")

You don't say where you are using this. If you are doing this in code, it is considered better to use an If, ElseIf structure or Select Case structure so that every portion of the whole expression is not evaluated and you have fewer restrictions compared to the Switch function. I'm not sure a lookup table will help if this has to to with testing for user generated conditions so you know how to direct program flow.

 

[mathematician]

I was meaning the way AND is written as a function

Yah I made a mistake in using AND function. Took Excel way. Do this, I tried and it worked

codeValue: Switch(

=123 And [Description]="ABC","TRUE",[code]=456 And [Description]="DEF","TRUE")

Switch is a simplification of Nested IIF but with switch you save time for u don't concentrate on closing "nested" brackets...