By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,548 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,548 IT Pros & Developers. It's quick & easy.

Report Sorting isn't working

100+
P: 443
I have a simple report that has two control sources (queries) each query has a different sort sequence. I'm using the db.querydef to change the query's sort option. When I run the report no sorting happens, I look at the query and it changes to the correct sort seq and if I just run the query everything looks good. I've checked into the sorting and Grouping and there is nothing there, I've also check the Order by On and it is set to Yes. The report has no sort seq at all so it's like it's not reading the query. Any help would be appreciated I'm at a loss.
Thanks
Aug 18 '09 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,628
@CD Tom
Set the Sorting and Grouping Options on the Report itself and you should be fine.
Aug 18 '09 #2

NeoPa
Expert Mod 15k+
P: 31,476
Reports always manage their own sorting in the Sorting & Grouping area. Go there as ADezii says, and all should be ok.
Aug 18 '09 #3

100+
P: 443
If I set the Sorting & Grouping to one of the sequences how do I change it to the other sequence the queries still don't work only the Sort & Grouping does.
Aug 18 '09 #4

ADezii
Expert 5K+
P: 8,628
CD Tom, how about posting the complete code?
Aug 18 '09 #5

100+
P: 443
Ok here's the db.querydefs
Expand|Select|Wrap|Line Numbers
  1.     If cmbReport = "Who's Registered" Then
  2.         stdmsg = "Sort by State? No will sort by Alias."
  3.         Vres = MsgBox(stdmsg, vbYesNoCancel, VMsgTitle)
  4.         If Vres = vbNo Then
  5.             Set Whosattending = db.QueryDefs("Whosattending")
  6.             Whosattending.SQL = "SELECT Master.SASS_Number, Master.ALIAS, States.State_name, Master.Current_match, Master.state, Params.Club_name, Params.RegionalName FROM Params, Master INNER JOIN States ON Master.STATE = States.State WHERE (((Master.Current_match)=True)) Order by Master.Alias ASC"
  7.             StDocName = "whosattend1"
  8.             DoCmd.OpenReport StDocName, acPreview
  9.         End If
  10.         If Vres = vbYes Then
  11.             Set Whosattending = db.QueryDefs("Whosattending")
  12.             Whosattending.SQL = "SELECT Master.SASS_Number, Master.ALIAS, States.State_name, Master.Current_match, master.state, Params.Club_name, Params.RegionalName FROM Params, Master INNER JOIN States ON Master.STATE = States.State WHERE (((Master.Current_match)=True)) Order by Master.State ASC"
  13.             StDocName = "whosattend1"
  14.             DoCmd.OpenReport StDocName, acPreview
  15.         End If
  16.         stdmsg = "Create a PDF from the Who's Registered Report?"
  17.         Vres = MsgBox(stdmsg, vbYesNo, VMsgTitle)
  18.         If Vres = vbYes Then
  19.             blRet = ConvertReportToPDF(StDocName, vbNullString, StDocName & ".PDF", False)
  20.         End If
  21.         Exit Sub
  22.     End If
the report whosattend1 uses the whosattending query.

Hope this is the code you were looking for.
Aug 18 '09 #6

Expert 100+
P: 1,287
Have you considered using:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.   Me.OrderBy = Forms![CallingFormName]!txtOrderChoice
  3.   Me.OrderByOn = True
  4. End Sub
Also, note this info from Allen Browne's website on the grouping and sorting affecting this.
Aug 18 '09 #7

NeoPa
Expert Mod 15k+
P: 31,476
As Chip's linked page suggests, you can use the report's GroupLevel array within code to control or change the sorting / grouping settings.
Aug 18 '09 #8

100+
P: 443
That worked great thanks, now I have another question regarding reports, Is there a way to change from portrait to landscape using code?
Aug 18 '09 #9

100+
P: 443
Thanks those tips are just what I needed.
Aug 19 '09 #11

ADezii
Expert 5K+
P: 8,628
@CD Tom
You are quite welcome.
Aug 19 '09 #12

NeoPa
Expert Mod 15k+
P: 31,476
@ADezii
Those tips are pretty darn impressive. Really useful as a reference :)
Aug 19 '09 #13

ADezii
Expert 5K+
P: 8,628
@NeoPa
Thanks NeoPa, just a remnant from my old 'Tipster' Days! (LOL)!
Aug 19 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.