Filter report from combo-boxes on form

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

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.

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.

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.

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!

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.

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?

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.

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. :)

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