IF and OR in combination

[KyleB]

Is there a way to do this:
=Iif([field]=OR("x","y","z"),true statement, false statement)

with the result of a text field being 12 possible options, and there are 4 equipment types possible to be listed in the 12 total. if the equipment is of type 1 (option 1,2,3,4) then do one thing, if the equipment is of type 2 (option 5,6,7), do another etc.

I could probably do this with an incredibly long, involved series of if statements, one for each of the 12 possible pieces of equipment, but I'd like to keep this as simple as possible.

 

[Fornatian]

if you want to use the textbox value in a forms code, use a select case statement like

Select Case Val([TheFieldName])

Case Is < 5 '1 to 4 selected
'1-4 action here

Case Is < 8
'5-7 action here

Case Is <12
'other action here

Case Else
Msgbox "The value was not recognised"

End Select

You can use text values in place of numbers but you'd do that like this:

Case "1st option","2ndOption","3rdOption"

It would be worth building a table with the twelve options there with the associated equipment type listed in a separate field.

The benefits are you have a central place to manage equipment and equipment types, you can then automatically interrogate the combo box column using Me![ComboName].Columns(1).Value to retrieve the equipment type.

If the equipment was to change in future how would you handle it - you'd have to re-engineer the program and from your employers p.o.v. they then have an unworkable db if you weren't around.

HTH

Ian

 

[KyleB]

Ok, using a separate table works, after I got to thinking about it I realized how I could set it up. Thanks, I think that solves the problem I was having.