Report from listbox filter

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

Oops.

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

Thanks!
xtab_2018-08-23--1-.accdb
Expected-result.png

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.

Hello,

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.
SelectionList.png
xtab_2018-08-23-V2.accdb
Report.png

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 & "#"

Select allOpen 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.

Hello,

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.
xtab_2018-08-24.accdb

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

But NOT BOTH.

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?
xtab_2018-08-25.accdb

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

So,
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.

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.

Hello,

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.

Welcome!