Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Sort (2007)

    I upgraded from Office 2003 Pro to Office 2007 Enterprise in February of 2007. Now I cannot get one of the reports in one of my databases to work right. It worked fine in Access 2003 up until January 2005 when new data for it stopped being acquired. After I entered some new data in November 2007 everything worked correctly, but I realized that due to operational changes the Inventory report must be different.

    The report was displaying an Item Number alphanumeric field followed by a Description text field and an Asking Price double field ordered by Item Number for table rows with a selling price of 0. It now needs to display the same fields ordered A-Z by description. I changed the underlying report query and when I run it the data sorts correctly, but the report still continues to be sorted by item number.

    I have checked all of the report settings and all of its code including the Open code below and I cannot find any place where the old sort order is specified.

    Private Sub Report_Open(Cancel As Integer)
    Dim enter As String
    Dim Mall As String
    Dim dbs As Database
    Dim r As Recordset
    Dim SqlText As String

    enter = Chr(13) & Chr(10)
    Set dbs = CurrentDb
    SqlText = "SELECT * FROM [Name of What Mall Information to Print]"
    Set r = dbs.OpenRecordset(SqlText)
    Mall = r.Fields("Mall").Value
    r.Close

    Me.lblHeading.caption = "Mom's " & Mall & " Inventory" & enter & _
    "as of " & Format(Date, "Short Date")

    Me.caption = Mall & Me.caption
    Me.RecordSource = Mall & " Inventory for Report" 'specify name of query based on mall name
    End Sub

    The mall name in the above code is stored via code in a module when the Print Inventory button on the switchboard is clicked.

    You may download a copy of the database with sample data at http://lila.godel.com/msgfiles/db.zip. When you open the database run the report tiled Inventory to see the incorrect sort.

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

    Re: Report Sort (2007)

    Reports mostly ignore the sort order set in their record source.

    You must specify the sort order in design view in the Sorting and Grouping window (there's a button for it on the toolbar, you can also use View | Sorting and Grouping).
    If that is not possible, you can specify the sort order in code (in the Report_Open event procedure)

    Me.OrderBy = "Description"
    Me.OrderByOn = True

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sort (2007)

    I got it working. As soon as I found the sorting & grouping tab you were refering to and clicked on the Group & Sort button I noticed a sort drop down that was set to Item Number. Once I changed that the new sort took effect.

Posting Permissions

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