Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Question Unanswered: User-defined categories in reports?

    Rather than have a certain category in my query sorted alphabetically, as the report wishes to do by default (and I need that category as a separate 'header'!) I'd like to sort it using a pre-defined order or, ideally, in the same order as is displayed in the combo box from which the value is selected in the forms and tables.

    Any suggestions as to how I'd do this?

    (I wrote a new thread because I feel that my other one doesn't give enough of an indication of what I need. Also, thank you to gavinfm for your suggestion, but I don't think that idea will work because I have already set up my forms and everything, and I think that would muck things up (unless I am getting the wrong idea about it, in which cause I'd be happy to hear about it. ))

    Thanks, from the ever-questioning


  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    One question: do you have grouping levels defined in your report?

    If you do NOT have any grouping levels, then you can pass a field name to the underlying SQL query in the ORDER BY clause. That's not to tough to do. However, if you do have grouping levels defined, you'll have to programmatically set the sort order for the grouping level itself.

    Assuming the actual column being ordered by is not going to change, eg you want the use to have the option to sort either A-Z or Z-A, then you could use code similar to this in the on open event of your report:
    dim OldSrc As String
    dim frm As Form
    dim Order As String
    OldSrc = Me.RecordSource
    Set frm = forms!yourForm
    If frm!yourSortOrder.value = "Ascending" Then
       Order = " ASC"
       Order = " DESC
    End If
    Me.RecordSource = OldSrc & Order
    This is of course assuming that you have the recordsource for the report statically defined up to the ASC/DESC switch. It should look something like this:

    SELECT *
    FROM yourTable
    WHERE someStuff
    ORDER BY yourField

    Then when you add the "Order" variable, you will effectivly be able to sort by user input.

    However, if you have grouping levels defined, you have to be a bit trickier:

    Dim frm As Form
    Set frm = forms!yourForm
    If frm!yourSortField.value = "Ascending" Then
       Me.GroupLevel(0).orderby = False
       Me.GroupLevel(0).orderby = True
    End If
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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