Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formatting top twenty sales people as bold (Access

    I have a simple report that lists all the sales people in a company with their total sales. The report is grouped on total sales without group headers to sort from the highest to the lowest sales. The client wants the top 20 sales people in bold, with the rest in regular type, and I don't have a clue as how to start. Would I need report event code?

    Thanks,

    Keith

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: formatting top twenty sales people as bold (Access

    You can use report Detail Format event for this. Example:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If FormatCount = 1 Then
    With Me
    With .Detail
    ' Bitwise comparison: returns 1 for odd, 0 for even numbers
    If Me.TXT_RUNSUM And 1 Then
    .BackColor = vbWhite 'Odd lines
    Else
    .BackColor = 14869218 'Even lines
    End If
    End With
    .OFF_NAME.FontBold = (.TXT_RUNSUM < 21)
    End With
    End If

    End Sub

    The relevant line is highlighted in bold. (The other code is for highlighting even lines in light grey.) For this to work, add an unbound text box (in example named "TXT_RUNSUM ") to report Detail section. Set textbox Visible property to No, ControlSource to =1, Running Sum to Over Group or Over All (depending on how report is set up; if report has various grouping levels, recommend use Over Group setting). In example OFF_NAME (Office Name) textbox appears in Bold for first 20 records only.

    A non-VBA alternative is to use Conditional Formatting for this. You still need the running sum text box as described above. Select textbox that will have the conditional formatting applied, then select Conditional Formatting from Format menu. See attached illustration of how to set using expression. This will highlight 1st twenty records listed in bold.

    HTH
    Attached Images Attached Images

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formatting top twenty sales people as bold (Access

    Thank you, Mark. Precisely what I needed...Keith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •