help with 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

Hi,

for the iif statement. What you can do is:
IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','')

Having a long nested if is a headache. What i can suggest is to add the field
to the table.

Help with complex nested iif statement wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Hi,

for the iif statement. What you can do is:
IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','STOCK')

Having a long nested if is a headache. What i can suggest is to add the field
to the table.

Help with complex nested iif statement wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

On Tue, 29 Dec 2009 17:59:01 -0800, Help with complex nested iif statement
Help with complex nested iif wrote:

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")

--

John W. Vinson [MVP]