If condition in Builder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to write If condition in Builder? I want to write: if a field = a certain
value, such as 1, then another field = a text statement, such as good. Can
anyone help!
 
You need to use the function IIf() (Immediate If) which can be selected from
the Expression Builder.

Check Access Help on the IIf() function.
 
liuqian said:
This is very helpful. What if I have multiple values, such as: 1 for good, 2
for bad, 3 for acceptable, 4 for under evaluation...How I should write the
expression? Thanks!
Click to expand...

You can nest IIf statements to a certain degree, though it can get
rather messy and difficult to follow the syntax and intent. Here is an
example of two levels of nesting:

IIf (Param=1, "good", IIf (Param=2, "bad", IIf (Param=3, "acceptable",
"unknown")))

Since you have more than two or three conditions to evaluate consider
these alternatives:

1) Build a table with two fields, one holds the parameter value, the
other the textual description. A simple query will return the
description you want.

2) Write a VBA function using the value as an input parameter and a
SELECT CASE list to return the appropriate description.
 
Smartin said:
You can nest IIf statements to a certain degree, though it can get rather
messy and difficult to follow the syntax and intent. Here is an example of
two levels of nesting:

IIf (Param=1, "good", IIf (Param=2, "bad", IIf (Param=3, "acceptable",
"unknown")))

Since you have more than two or three conditions to evaluate consider
these alternatives:

1) Build a table with two fields, one holds the parameter value, the other
the textual description. A simple query will return the description you
want.

2) Write a VBA function using the value as an input parameter and a SELECT
CASE list to return the appropriate description.
Click to expand...

Another alternative is the Choose statement:

Choose(Param, "good", "bad", "acceptable", "unknown")

(although I'd tend to recommend option 1 above)
 
Back
Top