You piqued my curiosity.Hi,
I have a list of customers. Some living at the same address. Is there a
way to use the label wizard to put more than one customer name on a label
using the same address?
If not, how can this be done.
J
fredg said:You piqued my curiosity.Hi,
I have a list of customers. Some living at the same address. Is there a
way to use the label wizard to put more than one customer name on a label
using the same address?
If not, how can this be done.
J
The following worked for me, with limited data.
You'll have to see how it works out for you.
I assume you alreay have a label report working.
If not, create one to use the labels you have.
Then....
Remove all of the labels you have in your label report.
Click on View + Sorting and Grouping
Group the report by the [Address] field.
Include a Group Header and a Group Footer.
In the Group Header, place the [Address] field control.
In the Detail Section, place the [FirstName] control.
Add an unbound control.
Name it "AllFirstNames'.
In the Group Footer place all the controls needed on the label (except
the [FirstName] control). Make them all not visible.
You can simply stack them one on top of the other. They won't be seen.
Add an unbound control.
Name it 'Combined'.
Set this control's CanGrow property to Yes.
I made it about 1/4" high. It's not critical.
Place it near the top of the Group Header section.
Size the Group Footer to the EXACT height of the Detail Section.
Set it's CanGrow and CanShrink properties to NO.
Code the relevant events, as per my code below.
(Watch for line breaks in the longer lines.)
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As
Integer)
Combined = Left([AllFirstnames], Len([AllFirstnames]) - 2) & " " &
[LastName] & vbNewLine & [Address] & vbNewLine & [City] & ", " &
[State] & " " & [ZIP]
End Sub
===============
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[AllFirstnames] = [AllFirstnames] & [First Name] & " & "
Cancel = True
End Sub
==============
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
[Combined] = ""
[AllFirstnames] = ""
Cancel = True
End Sub
==========
This should combine all First Names at the [Address] (John & Mary &
Steve) and print them from the Group Footer with the [Last Name] &
Address and City/State/ZIP.
Print one sheet on plain paper.
Hold it up against a sheet of labels. Look through a light source and
see how the label alignment is.
It should be OK. Adjust if needed.
As with any grouping, accuracy will depend upon accurate data entry.
123 Main St. is not the same a 123 Main Street, and 123 Rodeo Drive is
not the same as 123 Rodeo Dr.
Also, a label height is fixed, so, if you have many different first
names, you might have more lines needed than you have room for. One
method might be to count the number of ampersands in the AllFirstNames
control and change the font size if there are more than x number.
Also, I made no effort to account for a same street address but in a
different city and/or state. You can probably add additional groupings
for city and state (or just the Zip).
Just write Cancel = True in their Group Header Format event.
And lastly, some people residing at the same address might have
different last names. I'm sure you can figure out how to change the
above code and write each full name in the AllfirstNames control
instead of just the First Name. This will greatly limit the number of
names you can get onto a label since it's space is limited.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.