Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

Unbound Object Prompt

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.

Word Document within Access Report

I format the text in the document and exit out of Word mode and resize it for my Access Report.

Embedded Word document

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.

Address Fields in Report

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.

Print Preview of Addresses without formatting

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.

Print Preview of Address Missing 2nd Line

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.

Function as ControlSource in TextBox

The first party displayed is in perfect format for mailing.

Data Displayed Correctly

The second party missing Address2 is also displayed perfectly with a different number of lines in the address.

Data Displayed Correctly for Party missing Line 2