I need to create an IIF statement in an update query help!!

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

Guest

Column A has various ranges of numbers I want column B to show 1 against all
those that are < 9, 2 if they = 9, 3 if it is between 10 and 11 and 4 if the
ranges go beyond 12.
As much detail as possible please as I am not that hot on IIF statements.

Thankyou
 
This does the trick:

UPDATE Table1 SET b=iif([a]<9,1,iif([a]=9,2,iif([a]<12,3,4)))

- Raoul
 
Column A has various ranges of numbers I want column B to show 1 against all
those that are < 9, 2 if they = 9, 3 if it is between 10 and 11 and 4 if the
ranges go beyond 12.
As much detail as possible please as I am not that hot on IIF statements.
Click to expand...

I'd suggest using the Switch() function instead: it's better than IIF
for cases with more than two branches. It takes arguments in pairs,
and evaluates them left to right; the first time it finds the left
argument to be TRUE it returns the second of the pair and quits. E.g.

Switch( < 9, 2, = 9, 3, < 12, 4, True, 5)


John W. Vinson[MVP]
 
I agree :-)

- Raoul

John Vinson said:
Column A has various ranges of numbers I want column B to show 1 against all
those that are < 9, 2 if they = 9, 3 if it is between 10 and 11 and 4 if the
ranges go beyond 12.
As much detail as possible please as I am not that hot on IIF statements.
Click to expand...

I'd suggest using the Switch() function instead: it's better than IIF
for cases with more than two branches. It takes arguments in pairs,
and evaluates them left to right; the first time it finds the left
argument to be TRUE it returns the second of the pair and quits. E.g.

Switch( < 9, 2, = 9, 3, < 12, 4, True, 5)


John W. Vinson[MVP]
Click to expand...
 
Back
Top