Printing data horizontally instead of vertically on label



I need help creating Avery 5168 label that shows data in columns (accross)
and one Address on the bottom.
Prod1 Prod2 Prod3 Prod4 .....
Qty1 Qty2 Qty3 Qty4 .....

City, Sate, Zip

On a regular report, I can get the label to print multiple product IDs and
Quantities accross using multiple column settings in Page setup along with
creating a Footer for the company & grouping on the Company.
But, the address Information doesn't show up at the right place. Plus, I am
allowed only 4 labels per page.

I couldn't get much help from Label wizard either.
(prodid, qty, company, address... are coming from a query)

Can any one help?

Thank You in Advance,
M. Afzal


To place items of an order across the top of a label or report in the first
row and corresponding quantities below that (in the second row) followed by
address (shipping) information on the bottom of the label, use getToken
function available below or from your favorite web site.

Here is how I accomplished this task:

In the program code, take the order detail information for each customer,
concatenate multiple records (order line items) into a single string
separated by a delimiter such as comma (",") like "item1,item2,item3" and
save it to a field such as [Item] of a (new) table such as tblLabel. You
would do the same for quantities. But name and address information need only
have one copy in the same record (row) the items are in.
Example of the new table:
Item as text (255) item1, item2, item3
Qty as text (255) qty1, qty2, qty3
Customer as text (255) Joe Customer
Address as text(255) where he lives

Then, create a report (or label) and set its RecordSource property to the
new table that was previously created such as tblLabel.

Place multiple text box controls across the top of the Report/Label like so:
ItemTextbox1 ItemTextbox2 ItemTextbox3 ….
QtyTextbox1 QtyTextbox2 QtyTextbox3 ….

Set the ControlSource property of these controls to =gettoken([Item],",",x)
for first row and =gettoken([Qty],",",x) for the second row. X would be a
literal value corresponding to the number or location of the control for
example 1 or 2 or 3, etc.
For example, ControlSource properties for above textbox controls would be:
=gettoken([Item],",",1) =gettoken([Item],",",2) =gettoken([Item],",",3)
=gettoken([qty],",",1) =gettoken([qty],",",2) =gettoken([qty],",",3)

You would place textboxes for customer name and address information on the
bottom as normal and set their control source property directly to their
corresponding fields names such as =CustomerName, =address, etc.

More over:
There can only be limited number of controls on the labels for order items
(even if multiple rows are used). To be able to place more order items then
the capacity of a single label, multiple labels could be used. To span your
order across multiple labels, in your code, simply create additional records,
for each customer, if the number of items exceeds the number of controls
placed on the label. Report will automatically generate multiple labels to
fit all items on the order.

Known Limitations:
i) The limitation while trying to fit multiple order items across (even in
multiple rows) of the same label could be the size of the [item] and [qty]
fields in your table for example 255 characters if [item] and [Qty] was set
to string of 255.
ii) Integers are used in the function, so the number of tokens can not be
more then the size of Integer (about 32,767)
iii) It hasn’t be thoroughly tested, so use it at your own risk.

'***** FUNCTION GetTOKEN(String, String, Integer) String ****************
Public Function GetToken(txt As String, delimiter As String, location As
Integer) As String
'It returns nth token (specified by the location variable).
'Delimiter is assumed to be a single character such as “,†or “;â€, etc.
'getToken(SearchString, Delimiter_Char, Location_Int)
'For example,
'Dim myToken as String
'myToken = getToken ("ABC,DEF,GHI", ",", 3)
'myToken will be set to "GHI"
'Value of 0 or negative number for location, returns the original string.
'Value of location greater then the number of tokens in the string, returns
empty string.
'If either string or delimiter is empty, it returns the original string back.
'One of the features is that it doesn’t use an array that is why it is bit
'AUTHOR: Mohammad Afzal. Date 6-1-2005.

Dim pos As Integer, x As Integer, maxToken As Integer, startingPOS As
Integer, length As Integer
'*****Initial Error checking
If txt Like "" Or delimiter Like "" Then
GetToken = txt
Exit Function
End If
pos = InStr(1, txt, delimiter, vbTextCompare)
If pos < 1 And location <= 1 Then 'No token found to start with so return
original string
GetToken = txt
Exit Function
End If
'*****Count number of tokens in the string******************************
x = 0 'Counter
maxToken = 0 'Number of Tokens Found
pos = 0 'Position of Token or zero if not found
pos = InStr(pos + 1, txt, delimiter, vbTextCompare)
maxToken = maxToken + 1
If pos < 1 Then Exit Do
'*****Error Checking****************************************************
If location > maxToken Then 'If requested token is out of bounds, return
empty string.
GetToken = ""
Exit Function
End If
'*****Find starting location of requested token*************************
pos = 0 'Set location to beginning
For x = 1 To location - 1 '1st token doesn't have delimiter, so only runs
if location >2
pos = InStr(pos + 1, txt, delimiter, vbTextCompare)
Next x
startingPOS = pos + 1 'First token position is one passed the
' (assuming zero based strings)
'*****Find Length of the requested token********************************
If location = maxToken Then
length = Len(txt) - startingPOS + 1 'If last token, use length of string
'it won't have a delimiter
length = InStr(pos, txt, delimiter, vbTextCompare) - startingPOS
End If
'*****Return requested token********************************************
GetToken = Mid(txt, startingPOS, length) 'Kaboom
End Function