Multiple IIF statement for autofills on a form

[Schkeena]

HI,

I have a form that has a field titled ITEM and a field
titled MAJOR CATEGORY. I have figured out how to write
the IIF statement that will update the MAJOR CATEGORY
field to read EHRDW if the item AMP is selected.

My problem is that I have 130 possible item choices. I
need to create a multiple IIF query that would state that
if 1 of 100 possible ITEM choices is selected the MAJOR
CATEGORY is changed to EHRDW but if 1 of 30 other ITEM
choices is selected the MAJOR CATEGORY is changed to MHRDW.

Any help is greatly appreciated.

 

[Damien McBain]

HI,

I have a form that has a field titled ITEM and a field
titled MAJOR CATEGORY. I have figured out how to write
the IIF statement that will update the MAJOR CATEGORY
field to read EHRDW if the item AMP is selected.

My problem is that I have 130 possible item choices. I
need to create a multiple IIF query that would state that
if 1 of 100 possible ITEM choices is selected the MAJOR
CATEGORY is changed to EHRDW but if 1 of 30 other ITEM
choices is selected the MAJOR CATEGORY is changed to MHRDW.

Any help is greatly appreciated.

You can't nest that may Iif's and it wouldn't make sense to anyway because
all your options are hard coded if you do that. Try this:

Create a table called tblItems with 2 fields, "Item" and "MajorCat". Make
the field "Item" the primary key (presumably all the items are unique).

Make a combo box on your form called cboItem and set the row source to
tblItems. Set the columncount to 2, make the widths fit etc. Make a text box
on your form called txtMajorCat and set the control source:
=[cboItem].Column(1).

Now when you select an item from the combo, the text box txtMajorCat will
populate with the major category.

HTH,

Damo