Filter report from combo-boxes on form

DraganaRadic
DraganaRadic used Ask the Experts™
on
Hey,

I wanted to filter a report rtp_ReportProjects from the form frm_FilterReport. On this form there are 5 combo boxes and I wanted that once the user chooses a value in one or a few / all combo boxes the report generates only those records which match the selected values in fields City, CourtType, Intervention, ProjectStatus, Work (the 5 combo boxes).

For example, if I choose a City: Belgrade I would want only that record on the report which matches Belgrade.

I inserted a button OPEN REPORT; so on the event “on click” I wanted to run the Filter of Report.
Any ideas for the CODE?

Thanks!
27043271Filter-report-17.05.11.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would use the combos as selection criteria in the query that the report is based on.

You can then write each selection criteria cell as ..

Forms!frm_FilterReport!comboboxnamehere or Isnull(Forms!frm_FilterReport!comboboxnamehere)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
DraganaRadic,

This is by far the most common question here, it as been addressed countless times.
;-)

Do a search here of:
   Microsoft Access Filter Report Combobox Form

...for many examples of how this can be done.

;-)

JeffCoachman

Author

Commented:
Hey,
Peter > I ll try to look into your suggesstion. My report s not based on a query, jet these 5 combo boxes are taken from 4 different tables.

Jeff > I suppose the reason why there are some many similar question is that most users of this website find their problem/database to be special and have dificulties in shaping offered solutins to function on their databse. I have the same problem. I am a bit confused with all the one-to many relationships I have and these fields I always need for filtering therefore the solutions I found during my search in questions and answers didn t really work for me.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Peter > mistake, the report s based on a form.
A report cannot be based on a form.  Both forms and reports have to be based on a table or a query.  If it's currently a table then you can just build a query from that table and then change the report's recordsource.

It doesn't matter what the sources of the combo boxes are.

Author

Commented:
The thing is I have tables which are connected (and they are all a part of my report) with relatioinships and so I don t know on which one to make the query so that I can filter my report?

tbl_INTER_CourtsProjects
tbl_INTER_CourtInterventions
tbl_INTER_InterventionsWorks
tbl_SUB_Cities
tbl_SUB_TypesOfWorks

The report is currently based on a table.

Open the report in design view.
Click into the box in the top left cornere,it should have a black blob in it. Then diplay the report properties by clicking the Properties option on the ribbon.
In the Data properties, look at the Recordsource property. This is the table used by your report. Click the Build button (...) to the right and thgis will convert the source into a query.
You now need to drag all the fields into the query grid, and you set the criteria for each relevant column as I indicated in my first post.. Us eht correct combo box for each relevant column.

Forms!frm_FilterReport!comboboxnamehere or Isnull(Forms!frm_FilterReport!comboboxnamehere)

Close the query and say Yes you want to update the report when you are asked.

Author

Commented:
Peter> I made the query, i figured how to enter all the tables I needed. (tnx for the patience, i m soo new to this, everything s confusing to me)

So in the relevant fields of the query, for example City and CourtType in criteria I entered

[forms].[frm_FilterReport].[cmbCity]  

and

[forms].[frm_FilterReport].[cmbCourtType]    as you suggested

but smtg strange happens: when I only insert the criteria for the City field the report gets filtered. But when also add criteria for the CourtType, neither work. And vice versa, when I only enter criteria for CourtType it work and when I add for City neither work.

Do you maybe have an idea why this happenes?

and can you tell me what is this      or     Isnull(Forms!frm_FilterReport!cmbCity       part supposed to do?
Is it suppose to print all records if no value in the combo box is selected? Because if it is, it s not working. If I select nothing, the report is empty.

Tnx a lot!

Author

Commented:
Peter > 

I am attaching you my Database again so you can see that I (hopefully) inserted criteria right, but it doesn t work.

Look at the        qryReport        and          frm_FilterReport

you will see that I entered the criteria in 4 fields of the query.

When you try to filter from combo boxes on the       frm_FilterReport       you will see that only empty report appears.

Try to filter City with Belgrade, CourtType with BasicCourt, Intervention with Adaptation (I know these entrys exist in thedatabse) so if it all works they should be on the report.

27043271Filter-report-18.05.11-Q.accdb
Yes the second part deals with no selection, so no selection in all 4 columns should result in all records being returned.

Can you post the sql view of the query.

Author

Commented:
Here it is:

SELECT tbl_INTER_CourtsProjects.CourtNameID, tbl_INTER_CourtsProjects.CourtType, tbl_INTER_CourtsProjects.CourtCode, tbl_INTER_CourtInterventions.ProjectInterventionID, tbl_INTER_CourtInterventions.CourtNameID, tbl_INTER_CourtInterventions.ProjectStatus, tbl_INTER_CourtInterventions.InterventionValueEstimation, tbl_INTER_CourtInterventions.TimeEstimation, tbl_INTER_CourtInterventions.ShortDescriptionOfIntervention, tbl_INTER_CourtInterventions.TypeOfIntervention, tbl_INTER_CourtsProjects.City
FROM tbl_SUB_TypesOfWorks INNER JOIN (tbl_SUB_TypesOfIntervention INNER JOIN (tbl_SUB_TypeOfCourt INNER JOIN (tbl_SUB_Cities INNER JOIN (tbl_INTER_CourtsProjects INNER JOIN (tbl_INTER_CourtInterventions INNER JOIN tbl_INTER_InterventionsWorks ON tbl_INTER_CourtInterventions.ProjectInterventionID = tbl_INTER_InterventionsWorks.ProjectInterventionID) ON tbl_INTER_CourtsProjects.CourtNameID = tbl_INTER_CourtInterventions.CourtNameID) ON tbl_SUB_Cities.CityID = tbl_INTER_CourtsProjects.City) ON tbl_SUB_TypeOfCourt.TypeOfCourtID = tbl_INTER_CourtsProjects.CourtType) ON tbl_SUB_TypesOfIntervention.TypeOfInterventionID = tbl_INTER_CourtInterventions.TypeOfIntervention) ON tbl_SUB_TypesOfWorks.TypeOfWorkID = tbl_INTER_InterventionsWorks.TypeOfWorkID
WHERE (((tbl_INTER_CourtsProjects.CourtType)=[Forms]![frm_FilterReport]![cmbCourtType]) AND ((tbl_INTER_CourtInterventions.ProjectStatus)=[Forms]![frm_FilterReport]![cmbProjectStatus]) AND ((tbl_INTER_CourtInterventions.TypeOfIntervention)=[Forms]![frm_FilterReport]![cmbInterventions]) AND ((tbl_INTER_CourtsProjects.City)=[Forms]![frm_FilterReport]![cmbCity]));

If you delete the whole 'wher'e clause (remove all selection criteria) do you get any records returned?

Author

Commented:
If I delete the WHERE part, then there is no criteria for filtering.

If I go to frm_FilterReport  and leave all combo boxes empty I still get empty report.

I understand that if your remove the where clause then you cannot specify any criteria.

The point is, if you get no results when there is no Where clause then the problem is in the basic structure of your query not in the way you are setting the criteria.

You originally said that your report was based on a single table, and yet you have shown a quite complex query as the sql, using several tables.  At no point has anyone suggested that you change from a single table to a multi-table query. So I think we need to backtrack and establish what the starting point was.

Author

Commented:
The report is based on 3 tables.
The relatioinships can be seen in the database I attached.
So I use tbl_INTER_CourtsProjects, tbl_INTER_CourtInterventions and tbl_INTER_InterventionsWorks, plus tbl_SUB_Cities, tbl_SUB_TypeOfCourt, tbl_SUB_TypesOfIntervention and tbl_SUB_TypesOfWork

I mentioned that I am using fields to filter from different tables. In one of the posts I said that these one-to-many relationships I have many times, make my database kind of complicated.

I said table, meaning not form nor query, but I didn t say single table. :)

Author

Commented:
I figured it out ....
thanks for the help! you gave me the start!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial