If condition in Builder

L

liuqian

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!
 
V

Van T. Dinh

You need to use the function IIf() (Immediate If) which can be selected from
the Expression Builder.

Check Access Help on the IIf() function.
 
S

Smartin

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.
 
D

Douglas J. Steele

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)
 
Top