Problem sorting real time reports in Access?

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
I noticed, you do not specify the sort order, Asc or Desc.
Nitro187Author Commented:
1William:  Where do I specify the sort order?

creck:  Just tried that, no go.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Take a look at Help under "SortOrder Property"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitro187Author Commented:
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.
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.