MS Access Reports - OLE objects & Concatenate Function
MS Access Reports - OLE objects & Function to Concatenate Fields
This article shows how to embed a Word document into an Access Report and format it and also shows how to use a function to concatenate a mailing address in a single textbox.
Embedding a Word Document in MS Access
Create a new report and click in Design mode on the Unbound Object Frame and "draw" a Rectange in the Detail section of the report. A pop up box will prompt you to enter the Object type. I have selected Microsoft Word Document for this report and then click OK.
Double click on the box in your report and notice that the Ribbon has changed to the Word Ribbon with Page Layout, Design, and Home layouts. For this article, I inserted some Lorem Ipsum placeholder text.
I format the text in the document and exit out of Word mode and resize it for my Access Report.
Setting up a Dynamic Function in MS Access
This report will represent a document that is being sent out to a mailing list of people who have a Name, Address Line 1, City, State, Zip, and Country fields. Some of the people in the list also have an Address Line 2 field that contains data in it. If I use the Sales Person table that I created and put the fields down in the Main Report, it looks like this in Design Mode.
The first party has a perfect address with all fields. The presentation is not so nice looking as the City, State, and Zip should appear as it should for a piece of mail.
My next party is missing address line 2 and has a big gap in the middle of the address and that City, State, and Zip address line is still ugly.
A Function that will concatenate the addresses into the correct lines is needed to make this mail merge look nice. This code will detect the Length of each field passed to it and append it to a strAddress string that will be returned with the correct line breaks, comma, and spacing for our address label on the document.
Public Function BuildAddress(SalesName As String, Addr1 As String, Addr2 As String, Addr3 As String,
Addr4 As String, Addr5 As String, Addr6 As String)
Dim strAddress As String
If Len(SalesName) > 0 Then
strAddress = SalesName
End If
If Len(Addr1) > 0 Then
strAddress = strAddress & vbCrLf & Addr1
End If
If Len(Addr2) > 0 Then
strAddress = strAddress & vbCrLf & Addr2
End If
If Len(Addr3) > 0 Then ' city
strAddress = strAddress & vbCrLf & Addr3
End If
If Len(Addr4) > 0 Then 'state
strAddress = strAddress & ", " & Addr4
End If
If Len(Addr5) > 0 Then 'zip code
strAddress = strAddress & " " & Addr5
End If
If Len(Addr6) > 0 Then
strAddress = strAddress & vbCrLf & Addr6
End If
BuildAddress = strAddress
End Function
I add the fields to the side of the report and change the Visible property to No and then add 1 TextBox and type in =BuildAddress(SalesName, Address1, Address2, City, StateProv, ZipCode, Country) for the ControlSource.
The first party displayed is in perfect format for mailing.
The second party missing Address2 is also displayed perfectly with a different number of lines in the address.
