How do I use an elseif statement in Access 2003?

  • Thread starter Thread starter Nonsense
  • Start date Start date
N

Nonsense

I am in a select query. I am creating a new field [Sort] with the value
based on the current value in [status]. The value of the [status] field is
validated to only accept R, Y, or G:

Sort:iif ([status])="R", "1" elseif [status]="Y", "2" elseif [status]="G",
"3")

what am I doing wrong???
 
iif( status = "R", 1, iif( status = "Y", 2, 3))

or


switch( status="R", 1, status="Y", 2, status="G", 3, true, 0)


which is probably easier to maintain.


It may be even easier to have a table:


status code ' fields name
R 1
Y 2
G 3




and use lookup on field status to get the value under the field code. The
table allows you to add new status without having to edit code, which can be
nice if a user different than you can eventually have to use your
application.



Vanderghast, Access MVP
 
you dont use the world else it is automatically else

iif(condition,truestatement,falsestatement)

it you want an else replica

iif(condition,truestatement,iif(condition,truestatement,falsestatement))

or

iif(condition,iif(consition,truestatement,falsestatement),falsestatement))

or even

iif(condition,iif(consition,truestatement,falsestatement),iif(condition,truestatement,falsestatement))

and if you really want to get padantic

iif(condition,truestatement,iif(condition,truestatement,iif(condition,truestatement,falsestatement)))

as far as i knwo you can go to 10 levels of iifs

so what you want is

Sort:iif ([status])="R", "1",iif [status]="Y", "2",iif [status]="G",
"3","unknown")))

hope this helps

regards
kelvan
 
Back
Top