Need an expression that returns a certain value if another field .

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

Guest

How do I write an expression that returns a certain value if the numbers in
another field are in a certain range? For example, 1-10 would be a certain
dollar amount while >10 would be another.
 
Cheryl said:
How do I write an expression that returns a certain value if the numbers in
another field are in a certain range? For example, 1-10 would be a certain
dollar amount while >10 would be another.
Click to expand...


Using an expression is not a particularly robust approach,
but you can use the IIf function something like this:

Charge: IIf(qty > 10, .9 * Price, Price)
 
How do I write an expression that returns a certain value if the numbers in
another field are in a certain range? For example, 1-10 would be a certain
dollar amount while >10 would be another.
Click to expand...

If there are only two choices, use the IIF function: it takes three
arguments. The first argument is a logical expression which evaluates
to either TRUE or FALSE. If the argument is TRUE the function returns
the second argument; if FALSE it returns the third. So

IIF([Field] <= 10, 5.00, 4.00)

will return $5 if the field is 1-10 (or for that matter 0 or -33128),
$4 if it is greater.

If there are more than two, the Switch function is handy. It takes
arguments in pairs, and goes through them left to right. The first
time it hits a pair where the first argument is TRUE it returns the
second member of that pair and quits. E.g.

Switch([field] < 10, 5.00, [field] < 20, 4.00, [field] < 50, 3.00,
[field] < 100, 1.00, True, 0)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top