vXP - creating a report that has the same format but can be filtered

[mastro78]

I have a single report that has everything as it should. However, my user wants it available based on various dates (by quarter, by month, by year), and/or based on status (open, closed, on-going), and/or by manager, and/or by assistant manager, and/or by depart. With all those being able to intertwine, (ie) by manager, for quarter 2, all closed jobs. Is there a way to do something like this without having to create numerous reports and just create some type of sorting/grouping feature prior to opening the single report? Please advise, any help is muchly appreciated.

 

[dhookom]

I generally create a report filtering form that allows users to select a report from a list box and set various filtering based on text boxes, combo boxes, and list boxes.

Report names and titles are stored in a table and displayed on the form in a list box. The user can select any one report and click a command button to run.

I then use code to build a where condition to use in the OpenReport method.

  Dim strReport as String
  Dim strWhere as String
  strReport = Me.lboReport
  strWhere = "1=1 "
  If Not IsNull(Me.txtStartDate) Then
     strWhere = strWhere & " AND [TheDateField]>=#" & _
        Me.txtStartDate & "# "
  End If
  If Not IsNull(Me.txtEndDate) Then
     strWhere = strWhere & " AND [TheDateField]<=#" & _
        Me.txtEndDate & "# "
  End If
  If Not IsNull(Me.cboManager) Then
     strWhere = strWhere & " AND [Manager]=""" & _
        Me.cboManager & """ "
  End If
  '  more similar filters
  DoCmd.OpenReport strReport, acPreview, , strWhere

I some times kick this up a notch by using tables to store which reports use which criteria. I then enable or disable filtering controls based on which report is selected. Disable controls are then ignored when building the Where condition.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mastro78]

I have done something similiar to this. But I'm looking for a way to have a single report that reformats based on selected criteria...possibly via drop downs.

 

[dhookom]

Can you provide more specifics on what you mean by "single report that reformats based on selected criteria"?

I'm sure you have something specific in mind but you haven't provided this to us.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mastro78]

Like a report that has the fields: Status, Manager, Assist Manager, Dept, and Date (plus 4-5 other fields). When the user goes to the reporting form, there are drop downs that allow for a sort/group by the listed fields above. For example, Status=Closed, Manager=Mike, Year=2007. Once those are selected, the user clicks preview and the report is manipulated accordingly. This may not be a possible solution. I may need various reports to handle this, but was looking for a single report solution.

 

[dhookom]

mastro78,
There is a difference between sorting/grouping and filtering. You first mention sorting and then give an example of filtering.

I provided a solution above for filtering. Did you try it? Did you understand it?

Did you actually mean sorting which has nothing to do with filtering?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mastro78]

Actually, I'm using each term meaning something differed. In terms of filtering, meaning if by status and by year it filters out any record not relevant. And then sort accordingly. I apologize for the misunderstanding.

 

[dhookom]

Allen Browne has sample code on how to dynamically sort and group reports at run time at

http://www.allenbrowne.com/ser-33.html

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]