Report from listbox filter

Hello,

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?
SelectedItems.png
Report.png
xtab_2018-08-23--1-.accdb
David LelièvreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
For a quick response, upload a demo database demonstrating the issue.
Show the expected output.
0
David LelièvreAuthor Commented:
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
0
PatHartmanCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

David LelièvreAuthor Commented:
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
0
hnasrCommented:
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.
0
David LelièvreAuthor Commented:
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
0
PatHartmanCommented:
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.
0
David LelièvreAuthor Commented:
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
0
PatHartmanCommented:
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.
0
hnasrCommented:
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.
0
hnasrCommented:
Looks like it is working as to my understanding,
Please check and comment back.
Some event procedures were bypassed by adding Exit Sub just after Procedure name line.
You need to clean up the application.
xtab_2018-08-28.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David LelièvreAuthor Commented:
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.
0
hnasrCommented:
Welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.