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.
Select the fields to add to the individual labels. Click Next when done.
Select the fields to sort by from the Available fields list and click the arrow to add them to the Sort By list.
Click Next and enter a name for the report and click Finish to Preview it.
Fixing the Labels in the MS Access Report
The problem with the labels is that they are all on the same line.
This can be easily fixed in Design view by using the Chr function to reproduce a carriage return. Chr(13) + Chr(10).
By selecting Print Preview, the addresses are now breaking on each line and look like the labels that were intended to be printed.
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.
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
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).
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.
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.
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.