You need to create a table tblBoxSelect with three fields --
Product QTY Box_Size
Left join Invoice to tblBoxSelect on Product and Size.

Need_Box: IIF(tblBoxSelect.Box_Size Is Null, "Unknown",
tblBoxSelect.Box_Size)

Or

Need_Box: IIF(Invoice.Product = tblBoxSelect.Product AND Invoice.QTY =
tblBoxSelect.QTY, tblBoxSelect.Box_Size, "Unknown")


--
Build a little, test a little.


"Jim" wrote:

I need a way to loop through an invoice and figure out the number of boxes
being used per order. We have the same product that can go into multiple
size boxes depending on the quantity shipped. For example:



Widget A with a shipped quantity of 1 would go into box A which is 4X4X12

Widget A with a shipped quantity of 2-4 would go into box B which is 6X6X10

Widget A with a shipped quantity of 5 would go into both box A & B (1 each)



Thanks



Jim


.