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 Three Column Report with Optional Strikethrough Text

Microsoft Access - 3 Column Report

This article describes how to create a 3 column report in MS Access using the Label Wizard and by Page Setup. Also shows how to simulate strikethrough font text in the report when certain criteria is met.

Creating the MS Access Report

Click on the table or query and in the Menu bar go to Create and click on Labels in the Ribbon.

Label Wizard

Select the fields to add to the individual labels. Click Next when done.

Label Wizard Fields

Select the fields to sort by from the Available fields list and click the arrow to add them to the Sort By list.

Label Wizard Sort

Click Next and enter a name for the report and click Finish to Preview it.

Report Preview Labels

Fixing the Labels in the MS Access Report

The problem with the labels is that they are all on the same line.

Report Detail

This can be easily fixed in Design view by using the Chr function to reproduce a carriage return. Chr(13) + Chr(10).

Report Detail Modification Line Breaks

By selecting Print Preview, the addresses are now breaking on each line and look like the labels that were intended to be printed.

Report Preview with line breaks

Adding Strikethrough To Text Boxes in the MS Access Report

If you wanted to use strikethrough text on this list, it would not be possible as this feature is not available for the font property in Access.

If we create a new Access Report with the same table using separate text boxes, we can add Lines over each text box in the center and set the Visible property to No.

Report Add Lines

To make the report look like the Label Wizard report, go to the Page Setup and set the number of columns to 3 and the column layout

Report Page Setup for 3 Columns

To add the criteria that will determine when the strikethrough will occur, add a Textbox, set the Visible Property to No and select the Control Source to the criteria to use (in this case, it is Performance Rating).

Report Add Invisible Text Box with Criteria Property

Go to Design, View Code or click ALT + F11 to get to the Code behind the report. Here we can use the criteria fro the Performance Rating to determine when the strikethrough lines appear.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 If (Me.PerformanceRating = "Poor" Or Me.PerformanceRating = "Average") Then
  Me.lnName.Visible = True
  Me.ln1.Visible = True
  Me.ln2.Visible = True
  Me.ln3.Visible = True
 Else
  Me.lnName.Visible = False
  Me.ln1.Visible = False
  Me.ln2.Visible = False
  Me.ln3.Visible = False
 End If
End Sub

A Print Preview of the Report shows that the lines appear, however, they are striking through blank text boxes when the party lacks a 4th address line.

Report Print Preview Strikethrough

Fixing the Strikethrough in TextBoxes in the MS Access Report

With a couple of changes to the code, this can be fixed. By checking the length of the addresses in the last address line, the last line can be displayed selectively.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 If (Me.PerformanceRating = "Poor" Or Me.PerformanceRating = "Average") Then
  Me.lnName.Visible = True
  Me.ln1.Visible = True
  Me.ln2.Visible = True

 'code to determine when the line should appear

  If (Len(Me.Address3) > 0) Then
   Me.ln3.Visible = True
  Else
   Me.ln3.Visible = False
  End If
 Else
  Me.lnName.Visible = False
  Me.ln1.Visible = False
  Me.ln2.Visible = False
  Me.ln3.Visible = False
 End If
End Sub

The Print Preview of the Report now shows the correct strikethrough pattern.

Report Print Preview with Correct Strikethrough

Creating a 3 column report is easy with the Label Wizard. With only a few changes in VBA Code, strikethrough text can be simulated in Access reports.