Avatar of David Lelièvre
David Lelièvre
 asked on

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
DatabasesMicrosoft AccessVBASQL

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
Hamed Nasr

For a quick response, upload a demo database demonstrating the issue.
Show the expected output.
David Lelièvre

ASKER
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
PatHartman

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
David Lelièvre

ASKER
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
Hamed Nasr

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.
David Lelièvre

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

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.
David Lelièvre

ASKER
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
PatHartman

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Hamed Nasr

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.
ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
David Lelièvre

ASKER
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.
Hamed Nasr

Welcome!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.