Report from listbox filter

David Lelièvre
David Lelièvre used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

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

Author

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
Distinguished Expert 2017

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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
Hamed NasrRetired IT Professional

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

Author

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
Distinguished Expert 2017

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

Author

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
Distinguished Expert 2017

Commented:
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.
Hamed NasrRetired IT Professional

Commented:
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
Commented:
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

Author

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.
Hamed NasrRetired IT Professional

Commented:
Welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial