Print labels from query with column [TEXT] and [AMOUNT]

[killerflappy]

I want to print labels. These labels are created from a query.

At the moment the query has two columns.
1. The labeltext
2. The amount

For instance

[TEXT] [AMOUNT]
Address1 3
Address2 4
Address3 2

Is there a way to change or use his query so that I get the right amout of labels for each address?

A query that whith the result below would ben ice:

Address1
Address1
Address1
Address2
Address2
Address2
Address2
Address3
Address3

 

[plog]

Yes, kinda. It's going to take VBA to accomplish what you want. 2 methods:

1. Populate a temporary table and base the report on that table. Your VBA would first clear out your temp table, then loop through your query [AMOUNT] times for every [TEXT] and INSERT one record per loop in your temp table.

2. Create a dynamic query in VBA and base the report off of that. Similar approach--loop through your existing query to get the [AMOUNT] value and create UNION query that generates the results you want.

 

[isladogs]

Are you aware that you once you have sorted out your data source, you can use the label wizard to create a report for your chosen label type and size.

 

[killerflappy]

Are you aware that you once you have sorted out your data source, you can use the label wizard to create a report for your chosen label type and size.

Hi. I selected my query and then the Create/Labels but I don't see how to use the amount. Besides the table/query changes every week.

 

[June7]

I recommend plog's option 1. It's method I use (table is permanent, records are temporary). I think option 2 requires repeatedly creating and deleting query object. I avoid code that modifies database structure.

What query did you attempt? If you have not employed VBA procedure then you have not followed advice.

 

[Mark_]

Where do you get "Amount" from? Is this a sum of another table?

 

[killerflappy]

Where do you get "Amount" from? Is this a sum of another table?

Hi Mark,

We are importing an excel file from a customer. The amount is from a total column. So it's not a sum.

 

[Yev18]

I tend to go the dynamic route.

I have a table called Integers that has one column Num
with the values 0,1,2,3,4,5,6,7,8,9.

I then have a query called Numbers that generates all the numbers up to 10,000.

It has the Integers table added 4 times with no joins.

SELECT Integers.Num+Integers_1.Num*10 + Integers_2.Num*100 + Integers_3.Num*1000 AS Num
FROM Integers, Integers AS Integers_1, Integers AS Integers_2, Integers AS Integers_3
ORDER BY Integers.Num + Integers_1.Num*10 + Integers_2.Num*100 + Integers_3.Num*1000;

Adding it more times enables you to have more powers of 10 of course.

I then include the Numbers query into my label report query.
The criteria is that the number of labels you want to print is greater than Number.Num

So in your case in the design grid for column Amount

>[Numbers].[Num]

So if Amount is 4 you get rows where Num is 0, 1, 2 and 3 which is what you want.

 

[killerflappy]

Yes, kinda. It's going to take VBA to accomplish what you want. 2 methods:

1. Populate a temporary table and base the report on that table. Your VBA would first clear out your temp table, then loop through your query [AMOUNT] times for every [TEXT] and INSERT one record per loop in your temp table.

2. Create a dynamic query in VBA and base the report off of that. Similar approach--loop through your existing query to get the [AMOUNT] value and create UNION query that generates the results you want.

Thanks for the tips. I'm busy with option 1.

I don't know how to select a value of the current record in my INSERT-statement. For now I use 'test'.

Function CreateLabels()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim i As integer

Set db = CurrentDb
strSQL = "SELECT * FROM tblInfo WHERE barcode='label';"
Set rs = db.OpenRecordset(strSQL)
i = 1

Do While Not rs.EOF
    Do While i <= rs.Fields("AMOUNT")
        strSQL = "INSERT INTO tblLabels ( text ) SELECT 'test';"
        DoCmd.RunSQL (strSQL)
        i = i + 1
    Loop
    i = 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

 

[plog]

strSQL="INSERT INTO tblLabels(text) VALUES ('" & rs.Fields("TEXT") & "'"

 

[killerflappy]

This does the trick

strSQL = "INSERT INTO tblLabels VALUES (""" & rs!customer & " " & rs!Address & """);"

 

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications.