Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem sorting real time reports in Access?

Posted on 2003-03-18
6
Medium Priority
?
323 Views
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?

Thanks.
0
Comment
Question by:Nitro187
  • 3
  • 2
6 Comments
 

Expert Comment

by:creck
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
0
 
LVL 18

Expert Comment

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

Author Comment

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

creck:  Just tried that, no go.
0
Independent Software Vendors: 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!

 
LVL 18

Accepted Solution

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

Author Comment

by:Nitro187
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.
0
 
LVL 18

Expert Comment

by:1William
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!
0

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

577 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