IIF expressions

[Guest]

Hi,

I've written an expression where entering Yes into one field results in
another becoming Red. How can I add to this expression so that it can say Yes
will result in green, no will result in blue, maybe will result in yellow etc
etc

The expression is...

=IIf([Tracking Database]="Yes","RED")

How do I write an expression that says e.g. if field X = 3 and field N = B ,
field P = Red. Once this expression is done how do I then build on it so that
I can write numerous sets of results from differing values of X and N.

Be gentle with your responses please i'm a bit of a novice!

Cheers

Mike

Cheers

 

[Steve Schapel]

Mike,

You can use nested IIf() functions, something like this...
=IIf([Tracking Database]="Yes","Green",IIf([Tracking
Database]="No","Blue","Yellow")

An alternative is the Switch function, which is a bit simpler with
multiple conditions, e.g....
=Switch([Tracking Database]="Yes","Green",[Tracking
Database]="No","Blue",True,"Yellow")

For conditions involving more than one field, for example...
=IIf([field X]=3 And [field N]="B","Red")
....or, more commonly, to specify the 'falsepart' argument...
=IIf([field X]=3 And [field N]="B","Red","something else")

However, once you get a few conditions that you are trying to manage, it
is usually better to move into one of these scenarios:
1. Use VBA code to create a user-defined function which will return the
required value based on the input values.
2. Make a table that lists the pairs/sets of corresponding values, and
then this table is included in queries as required, in order to look up
the derived value from the existing.

Hope that's not too vague or obscure. If you need more explicit help,
you will possibly need to give fuller details of what you are actually
trying to achieve.

 

[Guest]

Hi Mike,

You can embed another IIF statement into both valuations of the statement.
Meaning you can for example write something like this:

IIF(X=1,IIF(B=2,"Yellow","Black"),"Green")

In the above the statement should the IIF statement evaluate to true then
the embedded IIF statement is evaluated. You can do the same for the false
side of the eval also.

One question to clarify though, you're mentioning colors here. Are you
wanting the fields to display the text "Red", "Green", "Yellow" or do you
want them to actually change to those colors? If you want them to change
colors then you are talking about conditional formatting. Works in much the
same way though.