Software

Conditionally underline values in an Access report

Make your Access reports easier to read by setting up conditional underlining. With conditional underlining, you can make information within certain parameters jump out in the report, specifying fields where certain criteria are met.

Detail reports become easier to read if the information you are looking for is highlighted in some way. For example, suppose you would like to know which of your employees worked more than 40 hours last week. You could search down a listing of employees and their hours and mark it yourself, or you could have Access do it for you. Follow these steps:

  1. Open the Employee Hours Report in Design mode.
  2. Click the Line tool in the Toolbox.
  3. Click and drag to draw a line under the Hours field in the Detail section.
  4. Right-click the line and select Properties.
  5. Click the All tab and click in the Name box and type FullTimeLine.
  6. Click in the Border Width Box and select 6pt.
  7. In the Database window, click Macros under Objects.
  8. Click the New button.
  9. Right-click the Macro title bar and select Conditions.
  10. Click in the first Condition cell and type [Hours]>39.
  11. Click in Item under Action Arguments and type [FullTimeLine].[Visible].
  12. Click in the Expression Box and type Yes.
  13. Click in the next Condition cell and type [Hours]<40.
  14. Click in Item under Action Arguments and type [FullTimeLine].[Visible].
  15. Click in the Expression Box and type No.
  16. Close and save the macro as Prtline.
  17. Open the Employee Hours Report in Design mode.
  18. Right-click a blank area of the Detail section and click Properties.
  19. Under the Event tab, click in the On Format property box and select Prtline.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

7 comments
marvinm
marvinm

The caveat being that even when the line is NOT visible, Access will still space the print line the same, which might leave an annoying gap. I think it would be cleaner to conditionally set the FontUnderline property of the Hours field(most likely a text box).

Merlirin
Merlirin

I went through the steps but when I was creating my macro, I did not get Action Arguments - this requires an Action. Is this a missing step or did I do it wrong?

pf1681
pf1681

How would I change the font and color of a field on a report going to the printer? The field is a text/memo [comments] and I want the font / color to change depending on what is in the [comments] field. Red background with black text for a "MISS" and green background with black text for "EXCLUDE". Other text is in the [comments] field as well so a wildcard would need to be used. I have spent a couple of hours trying to get this to work to no avail. Any help / ideas would be greatly appreciated.

M Cutler
M Cutler

I think there was a step left out between step 10 and 11, or this was for Acess 2007 only. step 10.5 should read "Select the SetValue" action (for 2003 or below at least). This will give you the action arguments needed to make this tip work. Cheers!

diane.milner
diane.milner

Here is the OnFormat [Event Procedure] code that I use to change the BackColor based on the value on the txtVARIANCE field in a report: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Me.txtVARIANCE = 0 Then Me.txtVARIANCE.BackColor = 16777215 'Transparent Me.txtVARIANCE.BackStyle = 0 'Transparent Me.txtVARIANCE.FontWeight = 400 'Normal ElseIf Me.txtVARIANCE < -0.00001 Then Me.txtVARIANCE.BackColor = 65535 'Yellow Me.txtVARIANCE.BackStyle = 1 'Normal Me.txtVARIANCE.FontWeight = 700 'Bold End If End Sub To modify the above code to fit your needs: Create a query qryComments_MISS where [comments] Criteria is like "*MISS*" Create a query qryComments_EXCLUDE where [comments] Criteria is like "*EXCLUDE*" If DCount("*", "qryComments_MISS")> 0 Then Me.comments.BackColor = 255 'Red Me.txtVARIANCE.BackStyle = 1 'Normal ElseIf DCount("*", "qryComments_EXCLUDE")> 0 Then Me.comments.BackColor = 32768 'Green Me.txtVARIANCE.BackStyle = 1 'Normal Else Me.comments.BackColor = 16777215 'Transparent EndIf

diane.milner
diane.milner

Here is the OnFormat [Event Procedure] code that I use to change the BackColor based on the value on the txtVARIANCE field in a report: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Me.txtVARIANCE = 0 Then Me.txtVARIANCE.BackColor = 16777215 'Transparent Me.txtVARIANCE.BackStyle = 0 'Transparent Me.txtVARIANCE.FontWeight = 400 'Normal ElseIf Me.txtVARIANCE < -0.00001 Then Me.txtVARIANCE.BackColor = 65535 'Yellow Me.txtVARIANCE.BackStyle = 1 'Normal Me.txtVARIANCE.FontWeight = 700 'Bold End If End Sub To modify the above code to fit your needs: Create a query qryComments_MISS where [comments] Criteria is like "*MISS*" Create a query qryComments_EXCLUDE where [comments] Criteria is like "*EXCLUDE*" If DCount("*", "qryComments_MISS")> 0 Then Me.comments.BackColor = 255 'Red Me.txtVARIANCE.BackStyle = 1 'Normal ElseIf DCount("*", "qryComments_EXCLUDE")> 0 Then Me.comments.BackColor = 32768 'Green Me.txtVARIANCE.BackStyle = 1 'Normal Else Me.comments.BackColor = 16777215 'Transparent EndIf

diane.milner
diane.milner

Here is the OnFormat [Event Procedure] code that I use to change the BackColor based on the value on the txtVARIANCE field in a report: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Me.txtVARIANCE = 0 Then Me.txtVARIANCE.BackColor = 16777215 'Transparent Me.txtVARIANCE.BackStyle = 0 'Transparent Me.txtVARIANCE.FontWeight = 400 'Normal ElseIf Me.txtVARIANCE < -0.00001 Then Me.txtVARIANCE.BackColor = 65535 'Yellow Me.txtVARIANCE.BackStyle = 1 'Normal Me.txtVARIANCE.FontWeight = 700 'Bold End If End Sub To modify the above code to fit your needs: Create a query qryComments_MISS where [comments] Criteria is like "*MISS*" Create a query qryComments_EXCLUDE where [comments] Criteria is like "*EXCLUDE*" If DCount("*", "qryComments_MISS")> 0 Then Me.comments.BackColor = 255 'Red Me.txtVARIANCE.BackStyle = 1 'Normal ElseIf DCount("*", "qryComments_EXCLUDE")> 0 Then Me.comments.BackColor = 32768 'Green Me.txtVARIANCE.BackStyle = 1 'Normal Else Me.comments.BackColor = 16777215 'Transparent EndIf