Solved

sort or filter before open report

Posted on 2008-10-14
13
313 Views
Last Modified: 2013-11-28
I have 4 fields, lastname, firstname, grade, schoolname
I created sql base on these fields. A report is based on this sql.
Now I would like to have user can selectable sort section , for example  they can choose sort by schoolname then lastname then by grade OR sort by grade then firstname then schoolname, etc....
Then report will show whatever user selected.
Thanks
0
Comment
Question by:qplo
  • 7
  • 3
  • 3
13 Comments
 

Author Comment

by:qplo
ID: 22716905
Any one can help me on this question?
0
 
LVL 84
ID: 22719038
You can set the OrderBy in the report's Open event. Your report can "look" at a form to determine what to sort by:

Sub Report_Open()

Me.OrderBy = Forms("YourFormName").SomeField
Me.OrderByOn

End Sub

If you're using Groups on your report, however, you may find that OrderBy doesn't work as intended, however.
0
 

Author Comment

by:qplo
ID: 22721959
I have 2 questions:
first "yourFormName"  you mean sql name or report name?

Run the code
Private Sub Report_Open(Cancel As Integer)
Me.OrderBy = Forms("ps1").grade
Me.OrderByOn
End Sub

Error: Compile error. invalid use property

Please help the correct error
0
 
LVL 84
ID: 22722586
Should be Me.OrderByOn = True

I have no idea what you mean by "sql name" ... YourFormName obviously refers to ... well ... Your Form Name.
0
 

Author Comment

by:qplo
ID: 22722635
this way is no flexible for user choose sort. I like to have a way user can select the way they want to sort.
0
 
LVL 84
ID: 22722718
I'm not sure what you're talking about, and for 30 points I've just about exhausted my time allotment for this question.

Your form could have a combo that included the various field names you want to be able to sort on, and the report could look to that combo for the field by which it should sort. I'm not sure how flexible you need to be, but giving a user the choice of sort fields would qualify, at least for my criteria. If you're looking to provide complete and robust filtering/sorting capabilities for your endusers then be prepared to spend a LOT of time on this ... in my most recent project fully 40% of development time was spent on building reporting filters and such.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:qplo
ID: 22724765
I'm sorry, OK, for example, the user only use interface form, that why i like to have user select the way they want to sort. I can do the sort from the query or on the report form, but the user not suppose to do it.
I saw someone done that before, but I can not find it anymore.
Like they have check box or list box to select field sort in order or something like that.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22726412
qplo,

The easiest way out of this might be to just have the Report pick up the sorting from the query.
(When you create the Report *Do Not* set any Sorting or Grouping options)

You would build the Report's RecordSource in VBA.

Here is a very basic sample I threw together quickly, from something I had done a whlie ago.

I am sure you can adapt this to work in your database.

JeffCoachman
AccessEEQ23813140ControlReportSo.mdb
0
 

Author Comment

by:qplo
ID: 22726628
boaq2000, please send me another one compatible with Access 2000, this one is unable top open. thanks
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 30 total points
ID: 22726715
0
 

Author Comment

by:qplo
ID: 22726844
YES, YES, this is exactly I would like to work on. Thank you for your help.
0
 

Author Closing Comment

by:qplo
ID: 31505930
great guy
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22727083
;-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access XML Export Query Setup Multiple Tag Values 15 30
Run Time Error 3071 26 39
Query design issue 2 22
Access coding 2 11
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now