IIF THEN

[Guest]

I have a list of about 20 parcel carriers and 40 or more LTL carriers. I want to group them by by "parcel" or "LTL" in my results, rather than list their individual carrier codes. So my query looks like this

ship type: IIf([Method of Shipment] = "B" Or "C" Or "G" Or "J" Or "K" Or "N" Or "O" Or "S" Or "U" Or "W" Or "X" Or "Y" Or "Z" Or "5" Or "6" Or "7" Or "8" Or "9","parcel","LTL"

It's not working. Without repeating the IIF THEN over and over, is there a way to group all 20 and represent them as parcel with the balance (not listed) represented as LTL? (Plus I'm not sure how many IF THENs can be used in one query.)

 

[Roger Carlson]

You have to repeat the field name:
IIf([Method of Shipment] = "B" Or [Method of Shipment] = "C" Or ...
This may run you into problems because this string is limited to 1024
characters.

On the other hand, you CAN use the IN operator:
IIf([Method of Shipment] IN ("B", "C", "G", . . ., "8",
"9"),"parcel","LTL")
Note: the (. . .) are just used to keep me from typing everything. You have
to literally type everything in the list.

But a better way would be to introduce a table with all of your method of
shipments in one column and the corresponding shipper in the other. Then
join the table and display and group on the shipper.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a list of about 20 parcel carriers and 40 or more LTL carriers. I

want to group them by by "parcel" or "LTL" in my results, rather than list
their individual carrier codes. So my query looks like this:

ship type: IIf([Method of Shipment] = "B" Or "C" Or "G" Or "J" Or "K" Or

"N" Or "O" Or "S" Or "U" Or "W" Or "X" Or "Y" Or "Z" Or "5" Or "6" Or "7" Or
"8" Or "9","parcel","LTL")

It's not working. Without repeating the IIF THEN over and over, is there

a way to group all 20 and represent them as parcel with the balance (not
listed) represented as LTL? (Plus I'm not sure how many IF THENs can be
used in one query.)