Print labels from query with column [TEXT] and [AMOUNT] (1 Viewer)

killerflappy · Aug 16, 2018

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 · Aug 16, 2018

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 · Aug 16, 2018

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 · Aug 16, 2018

ridders said:
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 · Aug 16, 2018

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_ · Aug 16, 2018

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

killerflappy · Aug 17, 2018

Mark_ said:
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 · Aug 17, 2018

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.

Code:

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

Code:

>[Numbers].[Num]

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

killerflappy · Aug 17, 2018

plog said:
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'.

Code:

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 · Aug 17, 2018

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

killerflappy · Aug 17, 2018

This does the trick

Code:

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

pbaldy · Aug 17, 2018

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

Print labels from query with column [TEXT] and [AMOUNT] (1 Viewer)

killerflappy

Registered User.

plog

Banishment Pending

isladogs

MVP / VIP

killerflappy

Registered User.

June7

AWF VIP

Mark_

Longboard on the internet

killerflappy

Registered User.

Yev18

New member

killerflappy

Registered User.

plog

Banishment Pending

killerflappy

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread