Filtering Sorting then Reporting Issue (1 Viewer)

ECEK · Feb 13, 2018

Morning Pat (well it is here !!!)

There is no save option once the user has viewed the report. They either print/save to file or close.

I'm a little unsure how to implement the where argument in regard to opening the report.

My current get around is to have two buttons:

If no filters or sorts are selected then:
Button1 opens a report1 with pre defined sort criteria that mimics the underlying query of the form.

Button opens report2 (your solution).

If only I could get your solution to print in the same way as my button1 (when nothing is filtered).

I tried to default report2 with sorting but this overides your solution

I also embarked on creating an If statement that was based on a sub form counting the records. If the number of records is the same as the number in the form then report1 else report2 but this still doesnt address the problem if the user simply resorts all of the data.

ECEK · Feb 13, 2018

Ridders:

I found this but can't make head nor tail of it.

Sorting report records at runtime
Access reports do their own sorting based on the sort fields you specify in the Sorting and Grouping dialog of the report. The recordsource Order By clause is ignored.

Microsoft has a knowledgebase article that explains a technique for using setting the OrderBy property of a report by opening the report in design view (Article ID: Q146310).

I have always preferred to programmatically set the group levels of the report, with code like this in the Open event of the report:

Select Case Forms!frmChooseSort!grpSort
Case 1 'Name
Me.GroupLevel(0).ControlSource = "LastName"
Me.GroupLevel(1).ControlSource = "FirstName"
Me.GroupLevel(2).ControlSource = "Company"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select

To make this work, you just need to make sure that you have set up the right number of grouping levels in the report's grouping and sorting dialog.

Is there a way we could incorporate this ?

Bear in mind that the sorting is not just A-Z but could also incorporate numerical formula eg less than etc

isladogs · Feb 13, 2018

Attached is an example showing how to setup sort & filter options for a report from a form.

There are 3 sort options & 5 filter options in this example so 15 possible outcomes from one report

Its hopefully fairly simple both to understand & then transfer the ideas to your own database.

There is no limit to the number of options you can do by this method.
For example, I have another report based on a crosstab query with 6*7= 42 possible sort & filter options

I had already done this before seeing your last post so haven't referred to that

isladogs · Feb 13, 2018

Just read your previous post.
It's a variation on what I've done.

Basically it's saying
1. If you are sorting by name then group by last name, first name and company in that order.
2. If sorting by company, group by company then lad name and finally by first name.

The report would have 3 grouping sections in the design.

ECEK · Feb 14, 2018

Ridders this is great stuff. I appreciate your time and patience greatly.

This isn't the solution that I wanted.
I'm flabbergasted that there isn't a simple solution to this.

What you see is what you get scenario.

My solution has been to create two buttons.
This is not ideal but works in the absence of a solution.

Two buttons: If you have filtered your data press button 1 if not button 2

button 1 is Pat's working solution.
button 2 a filtered (pre-sorted) Report

If any members can play with Pat's solution but enable a default (no sorts or filters) solution, then your posts are gratefully received.

isladogs · Feb 14, 2018

You do realise my solution included a no filter option

It would be very easy to adapt what I did to also include a no sort option.
However, that's a misnomer because Access will automatically sort by the first listed field if no sort is specified

ECEK · Feb 14, 2018

I think that the only way forward would be to implement an If statement somewhere!!!

If Pat's solution is invoked then open the filtered/sorted Report however if Pat's solution isn't invoked then open a report that is pre filtered/sorted.

Does Pat's coding give us the ability to flag the If. Is there a parameter to use?

ie
If Flag = "something" Then
open Pat's solution.
Else Open predefined.

Filtering Sorting then Reporting Issue (1 Viewer)

ECEK

Registered User.

ECEK

Registered User.

isladogs

MVP / VIP

Attachments

isladogs

MVP / VIP

ECEK

Registered User.

isladogs

MVP / VIP

ECEK

Registered User.

Similar threads

Users who are viewing this thread