Avatar of DraganaRadic
DraganaRadicFlag for Serbia

asked on 

Filter report from combo-boxes on form

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
Microsoft Access

Avatar of undefined
Last Comment
DraganaRadic
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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.
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

Peter > mistake, the report s based on a form.
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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.

SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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!
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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]));

Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

If you delete the whole 'wher'e clause (remove all selection criteria) do you get any records returned?
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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.

Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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

Avatar of DraganaRadic
DraganaRadic
Flag of Serbia image

ASKER

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo