help with nested iif statement

[Help with complex nested iif statement]

I have a table and I want to create a query that has a new field "
Type of Shipment" base on a field "Item Category"

If the item category field equals ZDIR, ZTRN ...etc up to 23 different item
categories the "Type of Shipment field will equal DIRECT else I would like it
to return "Stock"
Any help will be greatly appreciated.

Thanks

 

[John W. Vinson]

I have a table and I want to create a query that has a new field "
Type of Shipment" base on a field "Item Category"

If the item category field equals ZDIR, ZTRN ...etc up to 23 different item
categories the "Type of Shipment field will equal DIRECT else I would like it
to return "Stock"
Any help will be greatly appreciated.

Thanks

If the relationship between the category and the output is ever likely to
change, then I'd really suggest adding another field to the Item Categories
table containing "DIRECT" or "STOCK", and just use that field. Someday you
might want to add other values ("DISCARD", "INDIRECT", ...???), and you'll
surely want to be able to add or change categories without having to dig
through a nasty complicated IIF statement.

That said... try

TypeOfShipment: IIF([Category] IN ("ZDIR", "ZTRN", "..."), "DIRECT", "STOCK")