Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping in Report (2003)

    Hi,

    I am trying to create a report with grouping base on the valume of a field (Sign). Some data have Sign valume and some don't. How can I create gouping in the report base on null Sign filed or no null Sign field?

    Thanks

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping in Report (2003)

    You can group on an expression as well as on a field. You can enter the following expression in the Field/Expression box in the Sorting/Grouping window:

    IsNull([Sign])

    If you sort ascending, the records with a non-null Sign will be sorted before those with a null Sign, if you sort descending it'll be the other way round.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Hi Hans,

    I get Extra ) in query expression '[IsNull([Sign])" as I preview the report. I input the IsNull([Sign]) in the Field/Expression box in the Sorting/Grouping window.

    Please advise.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping in Report (2003)

    Try

    =IsNull([Sign])

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Thank, Hans. It's works.

    One more question, is any way to change the color of font for the grouping?

    Thanks

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping in Report (2003)

    You can change the font color of any control in design view. Or do you mean something else?

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Sorry for unlear question. I would like the report to auto change the font color of all data that grouping is IsNull([Sign]. If the Sign field have data, then all data fields font color is default.

    Thanks

    Regards,

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping in Report (2003)

    You'd have to write code for the On Format event of the Detail section:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngColor As Long
    Dim ctl As Control
    If IsNull(Me.Sign) Then
    lngColor = vbRed
    Else
    lngColor = vbBlack
    End If
    ' Suppress error message for controls that don't have a font color.
    On Error Resume Next
    For Each ctl In Me.Detail.Controls
    ctl.ForeColor = lngColor
    Next ctl
    End Sub

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    You could use conditional formatting as an alternative to VBA.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Thanks, Hans.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Hi Hans,

    One more question, I have a text box in IsNull([Sign]) Footer to count how many data in sign field. Since it will two different meaning of text box in the Footer, one label of text box call No Sign and the text box is 0. Another is Sign label with the number of count sign field data.

    How can I write the coder for On Format event of the Footer to change the name of label:

    1) "No Sign" label if Sign field is null
    2) "Sign" label if Sign field is not null

    Please advise.

    Thanks

    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping in Report (2003)

    Let's say that the name of the group footer is GroupFooter0 and that the name of the label is Label2.

    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    If IsNull(Me.Sign) Then
    Me.Label2.Caption = "No Sign"
    Else
    Me.Label2.Caption = "Sign"
    End If
    End Sub

    Replace GroupFooter0 and Label2 with the actual names.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping in Report (2003)

    Thank you so much, Hans.

Posting Permissions

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