UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Subform With Multiple Criteria On Same Field, Access 2016    
 
   
StarsFan
post May 15 2019, 08:07 AM
Post#1



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


Hello UA,

I have a dynamic filtering form, that I have been using for several years now, and it has worked very well. Recently I decided I would like to add a little more functionality to it, but I have had difficulty getting my head around the code. I have tried many versions and many searches but I am just not quite getting there.

The Layout: Main form with a subform. Subform contains our list of Sales Orders. I use the top of the main form as the filter criteria. I have many fields to filter the subform with. One of the fields is for what we call our Work in Progress. I would like to be able to search this one particular field for multiple criteria, so I created 4 different dropdown boxes all connected to the [Work In Progress WIP State] field. I would like to filter the subform based on these 4 different dropdown boxes and use the criteria on the field [Work in Progress WIP State]. Example: [Work in Progress WIP State] = 'ATP PPWK Pending' AND 'ATP Eval Scheduled'. I believe this is possible but I could be fooling myself. So far, I have not been able to get the code to work right. I will post what has come the closest to working for me below. I would appreciate any help someone could provide. FYI: I inherited the naming convention

If Trim(Nz(Me.cboWIPState, "")) <> "" Then
stFilter = stFilter & " AND [Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' AND '" & Replace(Me.cboWIPState, "'", "' '") & "'"
End If

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
June7
post May 15 2019, 08:13 AM
Post#2



Posts: 523
Joined: 25-January 16



AND operator won't work because no single record can meet that criteria. Either use OR operator or IN() function. A usual approach is a multi-select listbox but if you want to limit users to 4 selections then 4 comboboxes could accomplish the same. Regardless of which control, code must construct array of criteria for the IN() function. Review http://allenbrowne.com/ser-50.html

With OR, the field name must be repeated.

fieldname = this1 OR fieldname = this2 OR fieldname = this3 OR fieldname = this4

This post has been edited by June7: May 15 2019, 08:17 AM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
StarsFan
post May 15 2019, 08:28 AM
Post#3



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


Thanks much June. I replaced the 'AND' with 'OR' and it is still not filtering correctly. I am the only user, so changing to a multi-select listbox "might" could work. I only say it this way because I have many fields and I built the filter using strFilter and then filter the subform at the end using all the criteria I might possibly use. Translated, using a multi-select along with all the other stuff I have going on might be more than this poor boys brain can handle. If there was a way to make the 4 combo boxes work, I would like to go down that road. If that is not possible, then I will try my hand at the multi-select. Do you have any further recommendations on the combo boxes since AND and OR do not seem to be working?

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
June7
post May 15 2019, 08:41 AM
Post#4



Posts: 523
Joined: 25-January 16



Did you repeat the fieldname as described?

Be sure to enclose them all in a set of parens if you have other criteria involved.

other criteria AND (fieldname = this1 OR fieldname = this2 OR fieldname = this3 OR fieldname = this4)


This post has been edited by June7: May 15 2019, 08:44 AM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post May 15 2019, 09:00 AM
Post#5


UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA


One good way to clarify problems is to state what "not working" means. In this case, are you getting too many results, no results, wrong results, an error?

To repeat a point made before, you need to do something like "WHERE MyField = 1 OR MyField = 2 OR MyField = 3" and so on. Is that how you set it up?

One good way to troubleshoot could be to use Debug.Print to send the actual string resulting from your concatenation of options to the Immediate Window. Then you can copy that string into an actual query to see if there is a more obvious problem you can address.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
StarsFan
post May 15 2019, 09:02 AM
Post#6



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


Thanks, June, I really appreciate your help. I did forget to add the field names. I have done that now but I am not sure about where the parenthesis go.

This is what I have now:
stFilter = stFilter & " AND [Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '") & "'"

If I add parenthesis (like below) then I get a compile error:
stFilter = stFilter & " AND ([Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '") & "'")
stFilter = stFilter & " AND ([Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '")) & "'"

Sorry, I am mostly dangerous when it comes to coding.

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
GroverParkGeorge
post May 15 2019, 09:14 AM
Post#7


UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA


Again, a GREAT way to troubleshoot is to insert a debug.Print line in the code so you can see the actual string created by your concatenation. Often that's enough to spot the problem.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
StarsFan
post May 15 2019, 09:34 AM
Post#8



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


Thanks much, Mr. Grover. I am getting too many results when coded this way:
stFilter = stFilter & " AND [Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '") & "'"

I am not familiar with Debug.Print but did try to place that in my code but I am not sure where to look for the results. I did create a message box to show me the results of stFilter. It is below:
AND [Work In Progress WIP State] - 'R03-ATP Eval Needed' OR [Work In Progress WIP State] = 'R03.4-ATP PPWK Pending' AND [ATP] = 'Michael W.' AND [Status] = 'New'

This is what is used at the end of the code to strip the leading 'AND' and then run the filter
With Me.sfrmWIP_MasterList.Form
.FilterOn = False
.Filter = Mid$(stFilter, 5)
.FilterOn = True
End With

Thanks again for both of you lending a hand.

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
June7
post May 15 2019, 01:32 PM
Post#9



Posts: 523
Joined: 25-January 16



Parenthesis go around all the OR criteria as shown in my example.

stFilter = stFilter & " ([Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '") & "'"
stFilter = stFilter & " OR ([Work In Progress WIP State] = '" & Replace(Me.cboWIPState2, "'", "' '") & "' OR [Work In Progress WIP State] = '" & Replace(Me.cboWIPState, "'", "' '")) & "')"

Did you follow the Allen Browne example for conditionally building the criteria string?

This post has been edited by June7: May 15 2019, 01:39 PM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
StarsFan
post May 15 2019, 01:37 PM
Post#10



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


Thank you so much June!! That got it done and I really, really appreciate you hanging in there with me and helping me get past this. You are my hero!

Hope you get to laugh a lot today

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
June7
post May 15 2019, 01:40 PM
Post#11



Posts: 523
Joined: 25-January 16



I edited previous post, possibly after you read it. Review again.


--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
StarsFan
post May 15 2019, 02:36 PM
Post#12



Posts: 770
Joined: 7-February 03
From: Rendon, Texas


I nabbed the code years ago but I do believe it was from Allen Browne. I have gained a bunch because of him.

I will review again, per your request.

I really appreciate all you have done for me today. I have been trying to figure this part out for a very long time, so what you have done has made me want to do the happy dance.

--------------------
We laugh at honor, and are amazed that there are traitors in our midst. C.S. Lewis
Go to the top of the page
 
June7
post May 15 2019, 03:00 PM
Post#13



Posts: 523
Joined: 25-January 16



I see now you are stripping leading AND instead of trailing, so put that back into the first line but it does not belong on the second one.


--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th May 2019 - 07:53 PM