Problem sorting real time reports in Access?

Posted on 2003-03-18
Medium Priority
Last Modified: 2012-06-27
I am having a lot of troubles trying to make a report sort in order in MS Access.  Here is my code, can anyone help?  or is this a lost cause?


    If frmUnion = 1 Then
        strSQL = "SELECT tblCC.CCID, [GrievanceNum] & Chr$(13) & Chr$(10) & [oldgrievanceNUmber] AS GrievanceNumbers, tblCC.OldGrievanceNumber, tblCC.FilingDate, tblStep.Step, tblGrievanceType.Type, tblSubject.Subject, tblDirector.Director, tblDepartment.Department, tblSite.Site, ""Step "" & [tblStep].[Step] & "" - "" & [tblDateDetailsCC].[date] & Chr$(13) & Chr$(10) & [tbldatedetailsCC].[comment] AS Details, tblStatus.StatusDescription, tblUnion.UnionName, DatePart('yyyy',[filingdate]) AS Year, tblCC.CurrentStep, tblCC.EmployeeNames " _
                 & "FROM tblUnion INNER JOIN (tblSubject INNER JOIN (tblStep INNER JOIN (tblStatus INNER JOIN (tblSite INNER JOIN (tblGrievanceType INNER JOIN (tblDirector INNER JOIN (tblDepartment INNER JOIN (tblCC INNER JOIN tblDateDetailsCC ON tblCC.CCID = tblDateDetailsCC.CCID) ON tblDepartment.DepartmentID = tblCC.DepartmentID) ON tblDirector.DirectorID = tblCC.DirectorID) ON tblGrievanceType.TypeID = tblCC.TypeID) ON tblSite.SiteID = tblCC.SiteID) ON tblStatus.StatusID = tblCC.StatusID) ON tblStep.StepID = tblDateDetailsCC.Step) ON tblSubject.SubjectID = tblCC.SubjectID) ON tblUnion.UnionCode = tblCC.ContractID " _
                 & "WHERE tblCC.filingDate >= " & Format(cboDateFrom, JetDateFmt) & " AND tblCC.filingDate <= " & Format(cboDateTo, JetDateFmt) & " AND ("

    DoCmd.OpenReport "rptSQLReport", acViewDesign
    With Reports("rptSQLReport")
        'Sort 1st field
        If Not (Me.Combo46.Column(0) = " ") Then
            varGroupLevel = CreateGroupLevel("rptSQLReport", "sortField", False, False)
            .GroupLevel(varGroupLevel).ControlSource = Me.Combo46.Column(0)

        varGroupLevel = CreateGroupLevel("rptSQLReport", "sortField", False, False)
                                        .GroupLevel(varGroupLevel).ControlSource = "grievancenumbers"
        varGroupLevel = CreateGroupLevel("rptSQLReport", "sortField", False, False)
                                        .GroupLevel(varGroupLevel).ControlSource = "year"


I don't know what else to say, lol.

There is a lot more code, but pasting it all is redundant.  This code basically, searches the combo box that is there, and lets you select a field... then it sorts by the field you selected.

If anything, I want to at least be able to sort by one column... but can't even do that manually, by putting in a "1" or anything into the 'sort' column in the query.... so any ideas?

Question by:Nitro187
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 8159607
Hi nitro, try it like this:
1. docmd.openreport "rptSQLReport",acViewDesign,hidden
2. now you create your sorts after this save and close the report
3. open the report normal
LVL 18

Expert Comment

ID: 8159761
I noticed, you do not specify the sort order, Asc or Desc.

Author Comment

ID: 8160023
1William:  Where do I specify the sort order?

creck:  Just tried that, no go.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 18

Accepted Solution

1William earned 1500 total points
ID: 8160054
Take a look at Help under "SortOrder Property"

Author Comment

ID: 8162549
1William:  I searched for the SortOrder proprty, and that didn't help, but when it came up with the sort order property help, I noticed a button I didn't before on the "Report design" toobar, when I clicked the sorting and grouping button on it, it had a few sorting options in there that was messing it up. :P  Thanks for the help, that did the trick.
LVL 18

Expert Comment

ID: 8165978
Sorry I did not mention that.  looking at the code you presented, I was under the impression you wanted to do it all in code.  Glad you have it resolved!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question