We help IT Professionals succeed at work.

Report from listbox filter

Last Modified: 2018-08-28

I'm trying to generate a report from filtered form based on a date and a multi-select box of WorkerID (String).

I've got to the point where I can generate a report filtered by dates, but when I try to combine it with the listbox,

my report is not taking account the right selected items.

How can I fix this?
Watch Question

Hamed NasrRetired IT Professional

For a quick response, upload a demo database demonstrating the issue.
Show the expected output.



I wanted to upload the most recent file, but forgot to do it after deleting the old one.

Distinguished Expert 2017

We need to see the code behind the report button.  Are you using the WHERE or FILTER argument of the OpenReport method?  The report is not going to use the filters that the form does unless you tell it to.



You're right. I was not calling the filter.

I just have one question:

IE1: If I select 4 any list items and generate a report, the first 4 list items will be showing in the report instead of the 4 selected.
IE2: If I select the last 2 list items and generate a report, the first 2 list items will be showing in the report instead of the 2 select.

Why is this happening?

My goal was to generate a report based on the selected items, however it's acting on how many list items is selecting.

Thank you.
Hamed NasrRetired IT Professional

Two issues, Filtering and reporting.
1st issue, correct for filtering.
Modify this line:
Me.Filter = "[WorkerID] IN(" & strIN & ")" & " And " & "[DatePlanned] Between #" & Me.txtFromDT & "# And #" & Me.txtThruDT & "#"

Open in new window

It may be the same code problem for the report.
Check and comment back for this 2nd issue if you cannot sort it out.



The filtering part is working great!

But I still cannot pinpoint why even if strWhere is returning [WorkerID] IN ("B5455"),

 the report is getting the value "B1215".

Thank you.
Distinguished Expert 2017

You have code in the report's open event that is overriding the arguments sent by the OpenReport method. You have two techniques in play and you can only use one.  Use EITHER
1. Where argument in the OpenReport method
2. Modify Report's RecordSource



I added a criteria for the field WorkerID and I've modified the OnOpen event of the report.

However, when I ran the report, it says "No records match the criteria you entered."

And debug.Print strSQL is giving me: Select * from rReg where [WorkerID]='B5455'  though.

Why is that?
Distinguished Expert 2017

Don't you see the prompts?  they are coming from that code.  Looks like you took three suggestions and tried to use them all.  Pick one.

Why did you switch from using the where argument of the OpenReport to running code in the form's open event?  You should have been able to use the filter you built for the form in the OpenReport method as is.  OR, you could have used the filter as a WHERE argument in the OpenReport method.  There's lots of different methods to control how the report gets filtered.  Some work better for different situations.  I always prefer a WHERE clause to a Filter because I normally use a SQL Server BE.  With a Jet/ACE BE, it's six of one, half a dozen of the other.  However, with SQL Server, it makes a difference.  Filters are always applied locally so if your table is more than a few thousand rows, you'll pay a heavy price for forcing the server to retrieve all those rows you don't need and are getting rid of using a filter.  A where clause is sent to the server with the query and the server only returns the selected records.  So if your table has 100,000 rows and your filter would select 5 of them, the filter will bring down all 100,000 rows, apply the filter, and pass only the 5 rows to the report whereas the query with the where clause will only bring down 5 rows from the server.  This could be a huge difference in your processing speed.  If all the Access users are flooding the network with unused data, they are making the LAN unnecessarily slow for everyone.

To me, using one method is always better than having to remember two.  so I always use the WHERE clause (except in the example I sent you where I am specifically showing how to use a multi-select listbox).  Notice that the other example uses the WHERE clause.

When you build the SQL string in code, there is no reason to set the arguments.  As you build the string, you are concatenating in the actual data values.  The Access expression service has already turned them from objects into strings.   You only have to set the arguments if there are any (there are not) AND you are opening a recordset (which you should not be doing).

strSQL = "Select ... From ... Where myfield = Forms!myform!myfield"  --- will REQUIRE parameter declaration if you run this query using DAO.  But

strSQL = "Select ... From ... Where myfield = '" & Forms!myform!myfield & "'"  --- is embedding the value of the field rather than referencing an object that contains the value you want and so it is already a string with an embedded string and no further resolution is necessary.  You already told Access to concatenated the string from my field with the SQL string you built.

It's a difficult concept to get your head around.  I hope I made it clearer.

After building the SQL String, simply put it in the Report's Recordsource.  Nothing else.  No parameter setting.  No open recordset.  The report handles opening the recordset.  You do not.

The first version sends this string to the server:
"Select ... From ... Where myfield = Forms!myform!myfield"
The second sends this string:
"Select ... From ... Where myfield = 'ABC'"

ABC is an actual value that you want to search for but Forms!myform!myfield still needs to be evaluated by the query engine.
Hamed NasrRetired IT Professional

I modified report sections to English, and in code like Forms, and it printed a blank report after proper filtering.
Check for other names in functions and procedures.
Retired IT Professional
This one is on us!
(Get your first solution completely free - no credit card required)



Sorry for the late reply - I had some internet issues at home.

@Pat: Thank you for your detailed response and I'm sorry for being so slow headed.

@hnasr: It is indeed working like intended! Thanks!

I really appreciate your help.
Hamed NasrRetired IT Professional


Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.