Number of printed reports vary by entry in quantity field.

[Lele]

I am trying to create work tickets for our production people that vary by the
number of items to be produced. I have had sucess using a very helpful
suggestion which reappears below, but I am having a problem when I attempted
to write an IIF statement to double the quantity when I need to produce pairs.

I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= as you advised. Therefore if I am producing 2 pairs I
now get 4 work tickets. Great!

Here is the problem.
The tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Thank you so much for your help
--
Lele



--
Lele

I need to print work tickets for our production people that reflect the
number of items to be produced and help us track that we have produced and
shipped all items on the order.
For example if my order is for 3 pillows and 2 bedskirts, I need to produce
5 work tickets. I have been using this great code written by Graham Mandeno
and it works super. (see Below). I would like to refined the code as follows:

First: I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.
John W. Vinson[MVP]