Adjustable Mailing Lists 3

[perrymans]

I need to make a report that producing a mailing list for a directory. The problem is, not all fields have information. Some might be:

Name
Business
Business Address
Business Phone
Home Phone

And others could be:

Name
Business
Home Address
Home Phone

etc.

What is the best way to make a report for this?

I was thinking of something like grouping on the member's ID field and having a subform that was continuous.

Any thoughts?

Thanks. Sean.

 

[MajP]

Make a combined field concatenating the values.

Here is an example from Northwind data base using the Customers table

"SELECT  [CompanyName] & Chr(13) & Chr(10) & [Address] & Chr(13) & Chr(10) & [City]+ " " & [Region]+ " " & [PostalCode] AS Mailing
FROM Customers"

The results look like

Mailing

Alfreds Futterkiste
Obere Str. 57
Berlin 12209"

Ana Trujillo Emparedados y helados
Avda. de la Constitución 2222
México D.F. SA 05021"

Antonio Moreno Taquería
Mataderos 2312
05023

Around the Horn
120 Hanover Sq.
London England

Each one has a different piece of information missing.

You may notice that in certain places I use a "+" instead of and "&"

The reason is that a
null + a string = null

if you do
firstName & " " & MI & ". " & lastName

If you do not pass it a Middle intitial you would get
John . Smith
If you rewrite the above to
firstName & " " + MI & ". " & lastName
you get
John Smith
and if you pass it a MI
John A. Smith

 

[dhookom]

This is actually a Reports forum question, not a Forms question. What do you want to happen? Are the fields displayed across or down?

IMHO, never use a form or subform for printing.

You can set text boxes to Can Shrink if you don't want them to take up space on your report.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

MajP,
I'd replace this:
firstName & " " + MI & ". " & lastName
with this:
firstName & " " & (MI + ". ") & lastName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Yes, sorry that was a typo. The important point many people do not know

string & null = string
string + null = null

very handy in doing concatenations.

 

[perrymans]

Thanks. Neither the concatenation nor the Can shrink wil work.

There are fields that ae blank, and not always concatenatable. The can shrink still leaves noticable gaps in the result.

I will have to figure something out using a continuous detail style format.

Thanks. Sean.

 

[MajP]

Can you give an example? Especially an example of a field/s that can not get concatenated. I thought the concatenation example addresses blank fields.

 

[perrymans]

Name
Business Name
Business Address
Home Address

Let's say this is the format. Not all records have a Business Name and Address. This leaves blanks in the result like:

Name

Home Address

I would like to be able to automatically adjust for these types of missing pieces of information and produce:

Name 1
Business Name
Business Address
Home Address

Name 2
Home Address

Thanks. Sean.

 

[MajP]

Public Function concatAdd(varName As Variant, varBuisNam As Variant, varBuisAdd As Variant, varHomeAdd As Variant) As String
  If Trim(varName & " ") = "" Then
    Exit Function
  Else
    concatAdd = varName
  End If
  If Not Trim(varBuisNam & " ") = "" Then
    concatAdd = concatAdd & vbCrLf & varBuisNam
  End If
  If Not Trim(varBuisAdd & " ") = "" Then
    concatAdd = concatAdd & vbCrLf & varBuisAdd
  End If
  If Not Trim(varHomeAdd & " ") = "" Then
    concatAdd = concatAdd & vbCrLf & varHomeAdd
  End If
End Function

query or calculated control

SELECT concatAdd([Name],[BuisnessName],[BuisnessAddress],[HomeAddress]) AS mailing
FROM tblAddresses;

input

Name	BuisnessName	BuisnessAddress	HomeAddress
John Smith	Acme	123 1st Street	456 Main Street
Bob Smith		123 2nd Street	678 Front Street
Jan Smith			891 Elm Street
Rob Smith	ABC Printing	678 3rd Street

output

mailing
John Smith
Acme
123 1st Street
456 Main Street

Bob Smith
123 2nd Street
678 Front Street

Jan Smith
891 Elm Street

Rob Smith
ABC Printing
678 3rd Street

 

[perrymans]

That works great!

Thanks. Sean.