423,311 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,311 IT Pros & Developers. It's quick & easy.

Multiple search criteria on one junction table field

P: 13
I am building in MS Access 2013.

I have a table for Parts and a table for Tags which have a many-to-many relationship. I have created a junction table to eliminate the many-to-many issue. So I end up with:

tbl_Parts
PartID
PartType
Part Description
.
.

tbl_Tags
TagID
TagName
TagCategory

join_PartsToTags
ParttoTagID
PartID
TagID

I have built a form where the Tags appear in list boxes (by TagCategory). The user can select multiple values in each list box. This is all working.

I want the user to be able to filter the records they see by cascading through these TagCategory values. So, they can select multiple TagIDs in the first TagCategory then TagIDs in the second TagCategory. The results should only be Parts that meet ALL the selected tag criteria.

For example, they select TagIDs: IVV and LVV from TagCategory1 and TagIDs: 2015 and 2017 from TagCategory2. I want the results to be only parts that are associated with ALL the selected tags.

I know how to create a query with multiple criteria on different fields, but no idea how to do it when all the criteria applies to one field.

Thanks for any assistance.
Jun 26 '18 #1
Share this Question
Share on Google+
43 Replies


PhilOfWalton
Expert 100+
P: 1,290
Your Question is not all that clear to me, but I suspect your database is not normalised.
Does the same TagCategory appear more than say three times in your TblTags? If so you need a table of TagCategories.

Also in your TblJoinPartsTags, the ParttoTagID is not required. The PartID & TagID should be a joint Primary Key (Unique by definition)

Perhaps if you could give a bit more information such a a view of your relationship window (Please make sure it is readable) and some sort of indication what you would like your search form to look like, you may get some more help.

Phil
Jun 26 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,532
INJacqui,

It's difficult to help you in great detail as you have not provided a lot of information--nor shown any attempts at solving this yourself.

However, the heart of hte solutoin will be for you to build your filter string by cycling thorugh the selected items in the Listbox. This would be done by using the[ListBoxName].ItemsSelected property. You can learn more about this Property from the MS Developers Network here: ListBox.ItemsSelected Property. That should provide all you need to begin.

I am glad to help you troubleshoot your work, but we are not much in the habit on this forum of doing the work for you. We firmly believe that the best way to learn how to do something is to work through the problems yourself--and we are glad to guide you through the process.

Hope this hepps.
Jun 26 '18 #3

Rabbit
Expert Mod 10K+
P: 12,248
The basic idea would be to query the junction table for all parts with those tags in the first category. Count them by part and only return those with a count equal to the number of search terms.

Do the same for the second category. Join the 2 results and you'll be left with only the parts that have all tags for all categories searched.
Jun 26 '18 #4

P: 13
Sorry that I did not provide enough information. This is my first time posting in a forum like this, and I was trying to only provide the info pertaining to my specific question.

I also apologize if it appeared that I was wanting someone to provide the answer and the code to go along. I've been trying to figure this out for a couple of days and haven't been able to find anything that seems to be what I need. I'm really just looking for a nudge in the right direction.

The data is normalized - the TagCategory for the Tags table is linked through a one-to-many relationship.

I have VBA to loop through each of the TagCategory lists and builds a string for the selected TagIDs for each category. Here is a code snippet for one of those lists. All my variables are declared and I set the strEngineType to "" even though you don't see it in this snip.

Expand|Select|Wrap|Line Numbers
  1. ' Build Engine Type Filter
  2.     If strEngineType = "" Then
  3.         intLoop = 0
  4.         intCount = 0
  5.         For intLoop = 0 To Me.lstEngineType.ListCount - 1
  6.             If Me.lstEngineType.Selected(intLoop) = True Then
  7.                 intCount = intCount + 1
  8.             End If
  9.         Next intLoop
  10.  
  11.         If intCount > 0 Then
  12.             intET = 0
  13.             For intET = 0 To Me.lstEngineType.ListCount - 1
  14.                 If Me.lstEngineType.Selected(intET) = True Then
  15.                     strEngineType = strEngineType & "([TagID] = " & Me.lstEngineType.Column(0, intET) & ") Or "
  16.                 End If
  17.             Next intET
  18.             lngLen = Len(strEngineType) - 4
  19.             strEngineType = Left$(strEngineType, lngLen)
  20.             Me.txtEngineType = strEngineType
  21.         End If
  22.     End If
  23.  
So, for each TagCategory list, the criteria is OR. I want to capture every part that has any of those TagIDs.

The issue is that I have 3 different TagCategory lists and I want to join those as an AND situation. However, the data will never actually be AND because each record only has 1 TagID.

I am going to work on Rabbit's suggestion, and see what I can come up with. Any other nudges in the right direction would be greatly appreciated. Thanks!
Jun 27 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,532
I was thinking something more along the lines of this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private strFilter As String
  5.  
  6. Private Sub lstEngineType_AfterUpdate()
  7. On Error GoTo EH
  8.  
  9.     strFilter = ""
  10.  
  11.     CheckListBoxes
  12.  
  13.     With Me
  14.         .Filter = strFilter
  15.         .FilterOn = True
  16.     End With
  17.  
  18.     Exit Sub
  19. EH:
  20.     MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
  21.         "Error: " & Err.Number & vbCrLf & _
  22.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  23.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  24.     Exit Sub
  25. End Sub
  26.  
  27. Private Sub CheckListBoxes()
  28. On Error GoTo EH
  29.  
  30.     BuildFilter Me.Form.Name, "lstEngineType"
  31.     BuildFilter Me.Form.Name, "lstOtherListBox"
  32.     BuildFilter Me.Form.Name, "lstAnotherListBox"
  33.     BuildFilter Me.Form.Name, "lstDifferentListBox"
  34.  
  35.     Exit Sub
  36. EH:
  37.     MsgBox "There was an error checking the list boxes!" & vbCrLf & vbCrLf & _
  38.         "Error: " & Err.Number & vbCrLf & _
  39.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  40.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  41.     Exit Sub
  42. End Sub
  43.  
  44. Private Sub BuildFilter(FormName As String, ControlName As String)
  45. On Error GoTo EH
  46.     Dim frm     As Form
  47.     Dim ctl     As Control
  48.     Dim varItem As Variant
  49.  
  50.     Set frm = Forms!(FormName)
  51.     Set ctl = frm!(ControlName)
  52.     For Each varItem In ctl.ItemsSelected
  53.         If strFilter = "" Then
  54.             strFilter = "([TagID] = " & ctl.ItemData(varItem) & ") "
  55.         Else
  56.             strFilter = " OR ([TagID] = " & ctl.ItemData(varItem) & ") "
  57.         End If
  58.     Next varItem
  59.     Set ctl = Nothing
  60.     Set frm = Nothing
  61.  
  62.     Exit Sub
  63. EH:
  64.     MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
  65.         "Error: " & Err.Number & vbCrLf & _
  66.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  67.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  68.     Exit Sub
  69. End Sub
Your code cycles through each list box entry, whether it is selected or not. Mine is limited to those which are.

You would have a separate procedure for each of your list boxes, so that when any of them are updated, it cycles through all your list boxes for selections.

The Filter is also generated more simply.

Hope this hepps!
Jun 27 '18 #6

NeoPa
Expert Mod 15k+
P: 30,996
INJacqui:
Sorry that I did not provide enough information. This is my first time posting in a forum like this, and I was trying to only provide the info pertaining to my specific question.
We often forget that some careful consideration from a first-timer can be very similar to the vast majority that don't give any consideration at all. The fact that you did what you could to frame the question properly is enormously important. For us at least. Getting it right can be very hard even for old campaigners.

Now, you'll already have seen suggestions for helping you along your way. What I would say at this point is that Rabbit's suggestion (Post #4), though it's very brief and ultra-succinct (as is his way generally), is probably your most straightforward way forward.

If you don't understand what he's saying don't be afraid to ask for more detail or explanation. He's generally very loathe to be doing the work for you but he's happy to leave ideas out there for you to pick up and progress with. If you show an interest he'll generally drop more tips to help you find your way. As will others here of course when he's away and we understand well enough to advise.

That is not to say anything against the other suggestions of course, but working directly with SQL does tend to teach you more about the fundamentals of database work than working in VBA code generally. They're both important skills, but those that understand databases fully have a massive advantage when working with databases, so such understanding is invaluable.

In fact, I'll challenge Twinny & Phil, should they be interested of course, to take Rabbit's idea and run with it. I know they both have an interest in further developing that side of their understanding.
Jun 27 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,532
@ NeoPa and Rabbit:

Challenge accepted! Everything below are things I've learned from Rabbit.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private strFilter As String
  5.  
  6. Private Sub lstTagCategory1_AfterUpdate()
  7. On Error GoTo EH
  8.  
  9.     strFilter = ""
  10.  
  11.     CheckListBoxes
  12.  
  13.     With Me
  14.         .Filter = strFilter
  15.         .FilterOn = True
  16.     End With
  17.  
  18.     Exit Sub
  19. EH:
  20.     MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
  21.         "Error: " & Err.Number & vbCrLf & _
  22.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  23.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  24.     Exit Sub
  25. End Sub
  26.  
  27. Private Sub lstTagCategory2_AfterUpdate()
  28. On Error GoTo EH
  29.  
  30.     strFilter = ""
  31.  
  32.     CheckListBoxes
  33.  
  34.     With Me
  35.         .Filter = strFilter
  36.         .FilterOn = True
  37.     End With
  38.  
  39.     Exit Sub
  40. EH:
  41.     MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
  42.         "Error: " & Err.Number & vbCrLf & _
  43.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  44.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  45.     Exit Sub
  46. End Sub
  47.  
  48. Private Sub CheckListBoxes()
  49. On Error GoTo EH
  50.  
  51. On Error GoTo EH
  52.     Dim frm     As Form
  53.     Dim ctl     As Control
  54.     Dim varItem As Variant
  55.     Dim strCats As String
  56.     Dim intX    As Integer
  57.     Dim strSQL  As String
  58.  
  59.     Set frm = Forms!(FormName)
  60.     Set ctl = frm!lstTagCategory1
  61.     intX = 0
  62.     For Each varItem In ctl.ItemsSelected
  63.         intX = intX + 1
  64.         If strCats = "" Then
  65.             strCats = ctl.ItemData(varItem)
  66.         Else
  67.             strCats = ", " & ctl.ItemData(varItem)
  68.         End If
  69.     Next varItem
  70.  
  71.     strSQL = "SELECT tbl_Parts.PartID " & _
  72.         "FROM (SELECT join_PartsToTags.PartID, " & _
  73.         "Count(join_PartsToTags.PartID) " & _
  74.         "FROM join_PartsToTags " & _
  75.         "WHERE join_PartsToTags.PartID " & _
  76.         "IN (" & strCats & ")) " & _
  77.         "GROUP BY join_PartsToTags.PartID " & _
  78.         "HAVING Count(join_PartsToTags.PartID)=" & intX & ") AS Cat1 "
  79.  
  80.     Set ctl = frm!lstTagCategory2
  81.     intX = 0
  82.     For Each varItem In ctl.ItemsSelected
  83.         intX = intX + 1
  84.         If strFilter = "" Then
  85.             strFilter = ctl.ItemData(varItem)
  86.         Else
  87.             strFilter = ", " & ctl.ItemData(varItem)
  88.         End If
  89.     Next varItem
  90.     Set ctl = Nothing
  91.     Set frm = Nothing
  92.  
  93.     strSQL = strSQL & _
  94.         "INNER JOIN " & _
  95.         "((SELECT join_PartsToTags.PartID, " & _
  96.         "Count(join_PartsToTags.PartID) " & _
  97.         "FROM join_PartsToTags " & _
  98.         "WHERE join_PartsToTags.PartID " & _
  99.         "IN (" & strCats & ")) " & _
  100.         "GROUP BY join_PartsToTags.PartID " & _
  101.         "HAVING Count(join_PartsToTags.PartID)=" & intX & ") AS Cat2 " & _
  102.         "INNER JOIN tbl_Parts " & _
  103.         "ON Cat2 = tbl_Parts.PartID) " & _
  104.         "ON Cat1.PartID = tbl_Parts.PartID;"
  105.  
  106.     strFilter = "PartID IN (" & strSQL & ")"
  107.  
  108.     Exit Sub
  109. EH:
  110.     MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
  111.         "Error: " & Err.Number & vbCrLf & _
  112.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  113.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  114.     Exit Sub
  115. End Sub
I'm sure there is a more elegant, three-line version out there. And, I think it might tank if there are no categories selected.

Just an educated guess, without the ability to test. It assumes the tables are properly normalized, etc.
Jun 27 '18 #8

Rabbit
Expert Mod 10K+
P: 12,248
What I would say at this point is that Rabbit's suggestion (Post #4), though it's very brief and ultra-succinct (as is his way generally), is probably your most straightforward way forward.

If you don't understand what he's saying don't be afraid to ask for more detail or explanation. He's generally very loathe to be doing the work for you but he's happy to leave ideas out there for you to pick up and progress with. If you show an interest he'll generally drop more tips to help you find your way.
All true, I want to see how far someone can get with the general outline of the solution.

Now that I think about it. You don't need multiple queries. Since the tag ids between the different categories can't overlap, you only need one query appending all the lists together. Same solution, but without the need to build multiple queries.
Jun 27 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 2,532
I found the original question a bit confusing with TagIDs of "IVV", "LVV", "2015" and "2017". Just a strange combination of categories.

Rabbit, am I close? Using subqueries is a new(er) skill set for me....
Jun 27 '18 #10

Rabbit
Expert Mod 10K+
P: 12,248
@Twinnyfo, yes, I believe your subquery will work.

As far as the tags go, I believe that's why they have categories of tags. One category seems to be year, another seems to be type of item, and a third category I presume is make and model of the car. There's an argument to be made that it's been normalized too far. If every part has years, makes, and models that the part fits, then those should be in their own tables.
Jun 27 '18 #11

P: 13
Thanks so much for all the tips so far. I'll dive into them and see what I can figure out.

To clarify a little more, each part can have any number of Tags. The Tag Categories exist just to make searching for parts with certain tags a little easier for the end user. Rather than having to cycle though every tag, they can look at the tags by category.

I am able to get the results I want by building queries for each category and then joining those queries together to find the parts that appear in all. However, I can only get it to work if I actually type TagIDs in the query itself. I cannot yet figure out how to pass the comma delimited list from the form to the query so that it is read as individual TagIDs and not a single string.

I'll keep pushing through.

Thanks
Jun 27 '18 #12

NeoPa
Expert Mod 15k+
P: 30,996
Shout if you need more help. If you show what you've managed so far then we can direct any explanations more particularly to what you need.

PS. I love your attitude. With that you'll go far.
Jun 28 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 2,532
INJacqui,

Please review lines 62-69 and Lines 82-89 of the code posted in Post #8. I beleive this is the means of getting the comma delimited values into your query/filter string.

Let us know if this gets you down the right path.
Jun 28 '18 #14

P: 13
Twinnyfo - thanks for the direction. I was thinking about it in the shower last night and realized that the code you provided would direct me as to how to pull the tagIDs from the string. The one thing I am confused about in your code is the Count. What is the count doing?
Jun 28 '18 #15

P: 13
OK...I used your suggestions to build individual queries outside of VBA. I believe I know understand what you are doing with the COUNT function. I've got some good ideas of how to move forward.

I very much appreciate all the help! I'll get back to you and let you know if I'm able to get it working or if I need more help.

Thanks again.
Jun 28 '18 #16

Rabbit
Expert Mod 10K+
P: 12,248
One thing you should be aware of is that twinny's code works by filtering the form. Which takes the dynamically created SQL and plugs it into the filter property of the form. The form will then parse the SQL in that property.

This does not work if you want to plug that string into a query. To get it to work in a query, you would have to either have the code modify the query definition directly or switch the filtering criteria from using the IN operator to using the LIKE operator.
Jun 28 '18 #17

P: 13
Rabbit - thanks for the clarification. I thought that was what it was doing, but it's always good to get confirmation.

I am using twinny's code exactly as written, and it runs until line 36

.FilterOn = True

Then I get the following error:
Run-time error '2176'
The setting for this property is too long.


Should strFilter set in line 106 actually be the list of PartIDs that are the results from the SQL statement? Right now, when I debug, strFilter is st to the SQL statement built from lines 93 - 104.

I've read and re-read the code trying to figure out what might be wrong, but I'm at a loss.

Thanks
Jun 29 '18 #18

twinnyfo
Expert Mod 2.5K+
P: 2,532
I guess the SQL is too long to include as a filter string. I guess you could open a record set using the SQL statement then build the filter that way.
Jun 29 '18 #19

NeoPa
Expert Mod 15k+
P: 30,996
Hi. Are you sure you have the correct line reported, as that one is pretty simple and straightforward.

If, as I suspect, it's actually the line setting the .Filter property, then could you post the actual value here for us to review for you please.
Jun 29 '18 #20

Rabbit
Expert Mod 10K+
P: 12,248
It's possible it doesn't try to parse it until you try to turn the filter on.

If there's an upper limit on the length of the filter property, then you may have to return the search results a different way, such as by using a query and opening that up in a different form or requerying a subform.
Jun 29 '18 #21

P: 13
Sorry, I've been away from the project for a couple of days.

Here's the code that I copied from Twinny's suggestion. Again, it runs until I apply the filter.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private strFilter As String
  5.  
  6. Private Sub lstEngineType_AfterUpdate()
  7. On Error GoTo EH
  8.  
  9.     strFilter = ""
  10.  
  11.     CheckListBoxes
  12.  
  13.     With Me
  14.         .Filter = strFilter
  15.         .FilterOn = True
  16.     End With
  17.  
  18.     Exit Sub
  19.  
  20. EH:
  21.     MsgBox "There was an error with the list box."
  22.     Exit Sub
  23.  
  24. End Sub
  25.  
  26. Private Sub lstModelYear_AfterUpdate()
  27. On Error GoTo EH
  28.  
  29.     strFilter = ""
  30.  
  31.     CheckListBoxes
  32.  
  33.     With Me
  34.         .Filter = strFilter
  35.         .FilterOn = True
  36.     End With
  37.  
  38.     Exit Sub
  39.  
  40. EH:
  41.     MsgBox "There was an error with the list box."
  42.     Exit Sub
  43.  
  44. End Sub
  45.  
  46.  
  47. Private Sub CheckListBoxes()
  48. On Error GoTo EH
  49.  
  50.     Dim db As Database
  51.     Dim rsResults As Recordset
  52.  
  53.     Dim frm As Form
  54.     Dim ctl As Control
  55.     Dim varItem As Variant
  56.     Dim strCats As String
  57.     Dim strCats2 As String
  58.     Dim intX As Integer
  59.     Dim strSQL As String
  60.     Dim strSQL_ET As String
  61.     Dim strSQL_MY
  62.  
  63.  
  64.  
  65. ' Engine Type
  66.     Set frm = Forms!frm_BuildCart_Bytes2
  67.     Set ctl = frm!lstEngineType
  68.     intX = 0
  69.  
  70.     For Each varItem In ctl.ItemsSelected
  71.         intX = intX + 1
  72.         If strCats = "" Then
  73.             strCats = ctl.ItemData(varItem)
  74.         Else
  75.             strCats = strCats & ", " & ctl.ItemData(varItem)
  76.         End If
  77.     Next varItem
  78.  
  79.     strSQL = "SELECT tbl_Parts.PartID " & _
  80.         "FROM (SELECT join_ParttoTag.PartID, " & _
  81.         "Count(join_ParttoTag.PartID) " & _
  82.         "FROM join_ParttoTag " & _
  83.         "WHERE join_ParttoTag.PartID " & _
  84.         "IN (" & strCats & ")) " & _
  85.         "GROUP BY join_ParttoTag.PartID " & _
  86.         "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
  87.  
  88. ' Model Year
  89.     Set ctl = frm!lstModelYear
  90.     intX = 0
  91.  
  92.     For Each varItem In ctl.ItemsSelected
  93.         intX = intX + 1
  94.         If strFilter = "" Then
  95.             strFilter = ctl.ItemData(varItem)
  96.         Else
  97.             strFilter = strFilter & ", " & ctl.ItemData(varItem)
  98.         End If
  99.     Next varItem
  100.     Set ctl = Nothing
  101.     Set frm = Nothing
  102.  
  103.  
  104.     strSQL = strSQL & _
  105.         "INNER JOIN " & _
  106.         "((SELECT join_ParttoTag.PartID, " & _
  107.         "Count(join_ParttoTag.PartID) " & _
  108.         "FROM join_ParttoTag " & _
  109.         "WHERE join_ParttoTag.PartID " & _
  110.         "IN (" & strCats & ")) " & _
  111.         "GROUP BY join_ParttoTag.PartID " & _
  112.         "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
  113.         "INNER JOIN tbl_Parts " & _
  114.         "ON Cat2 = tbl_Parts.PartID) " & _
  115.         "ON Cat1.PartID = tbl_Parts.PartID;"
  116.  
  117.         strFilter = "PartID IN (" & strSQL & ")"
  118.  
  119.     Exit Sub
  120.  
  121. EH:
  122.     MsgBox "There was an error building the filter."
  123.     Exit Sub
  124.  
  125. End Sub
Here's snip of the error.



One question I have is about the strCats and strFilter.
When building the first strSQL, we are using strCats to store the selected items from the list as well as for the IN statement. However, when building the second strSQL, we are using strFilter to store the selected items from the list but strCats for the IN statement. Can you help me understand what I'm missing? I would think that we would have to either use strFilter for the IN statement or strCats to store the selected items.

Thanks!
Jul 2 '18 #22

twinnyfo
Expert Mod 2.5K+
P: 2,532
Use this as your new CheckListBoxes Sub:

Please note there are a few changes. We now use the SQL statement to open a recordset and build the filter itself.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckListBoxes()
  2. On Error GoTo EH
  3.     Dim strETs      As String
  4.     Dim strMYs      As String
  5.     Dim frm         As Form
  6.     Dim ctl         As Control
  7.     Dim intX        As Integer
  8.     Dim varItem     As Variant
  9.     Dim strSQL      As String
  10.     Dim db          As DAO.Database
  11.     Dim rst         As DAO.Recordset
  12.     Dim strPartIDs  As String
  13.  
  14.     strETs = ""
  15.     strMYs = ""
  16.  
  17. ' Engine Type
  18.     Set frm = Forms!frm_BuildCart_Bytes2
  19.     Set ctl = frm!lstEngineType
  20.     intX = 0
  21.  
  22.     For Each varItem In ctl.ItemsSelected
  23.         intX = intX + 1
  24.         If strETs = "" Then
  25.             strETs = ctl.ItemData(varItem)
  26.         Else
  27.             strETs = strETs & ", " & ctl.ItemData(varItem)
  28.         End If
  29.     Next varItem
  30.  
  31.     strSQL = "SELECT tbl_Parts.PartID " & _
  32.         "FROM (SELECT join_ParttoTag.PartID, " & _
  33.         "Count(join_ParttoTag.PartID) " & _
  34.         "FROM join_ParttoTag " & _
  35.         "WHERE join_ParttoTag.PartID " & _
  36.         "IN (" & strETs & ")) " & _
  37.         "GROUP BY join_ParttoTag.PartID " & _
  38.         "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
  39.  
  40. ' Model Year
  41.     Set ctl = frm!lstModelYear
  42.     intX = 0
  43.  
  44.     For Each varItem In ctl.ItemsSelected
  45.         intX = intX + 1
  46.         If strMYs = "" Then
  47.             strMYs = ctl.ItemData(varItem)
  48.         Else
  49.             strMYs = strMYs & ", " & ctl.ItemData(varItem)
  50.         End If
  51.     Next varItem
  52.     Set ctl = Nothing
  53.     Set frm = Nothing
  54.  
  55.     strSQL = strSQL & _
  56.         "INNER JOIN " & _
  57.         "((SELECT join_ParttoTag.PartID, " & _
  58.         "Count(join_ParttoTag.PartID) " & _
  59.         "FROM join_ParttoTag " & _
  60.         "WHERE join_ParttoTag.PartID " & _
  61.         "IN (" & strMYs & ")) " & _
  62.         "GROUP BY join_ParttoTag.PartID " & _
  63.         "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
  64.         "INNER JOIN tbl_Parts " & _
  65.         "ON Cat2 = tbl_Parts.PartID) " & _
  66.         "ON Cat1.PartID = tbl_Parts.PartID;"
  67.  
  68.     Debug.Print strSQL
  69.  
  70.     strFilter = ""
  71.     Set db = CurrentDb()
  72.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  73.     With rst
  74.         If ((Not .BOF) And (Not .EOF)) Then
  75.             .MoveFirst
  76.             Do While Not .EOF
  77.                 If strFilter = "" Then
  78.                     strFilter = !PartID
  79.                 Else
  80.                     strFilter = strFilter & ", " & !PartID
  81.                 End If
  82.                 .MoveNext
  83.             Loop
  84.         End If
  85.         .Close
  86.     End With
  87.  
  88.     Debug.Print strFilter
  89.  
  90.     Exit Sub
  91. EH:
  92.     MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
  93.         "Error: " & Err.Number & vbCrLf & _
  94.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  95.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  96.     Exit Sub
  97. End Sub
Hope this hepps!
Jul 2 '18 #23

P: 13
Thanks for the new approach. I'm still having some trouble with the SQL statement, but I'm working through it a bit at a time. I guess it's about time for me to really understand SQL :)
Jul 4 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 2,532
What "troubles" are you having with the SQL statement?

That is very vague and we can't offer any advice if we don't know the issue. Is there an error popping up? Or, do you just not understand the approach?
Jul 5 '18 #25

P: 13
Hey, twinnyfo,

I was trying to see if I could psych it out first. I really am trying to improve my SQL skills. I've put it off long enough :)

Anyway, the error I am receiving is

Run-time error 3075:
Extra ) in query expression 'Count(join_ParttoTag.PartID)=1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag.PartID, Count(join_ParttoTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag.PartID IN ()) "GROUP BY join_ParttoTag.PartID HAVING Count(join_ParttoTag.PartID)=0) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartID) ON Cat1.PartID = tbl_Parts.PartID;


When I debug, the error is thrown on this line:
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)


This happens no matter if I have items selected in both lists or only in one or the other.

Any insight would be greatly appreciated. I've tried to parse out the subqueries and figure out where the extra ) is, but I've had no luck.

Thanks!
Jul 5 '18 #26

twinnyfo
Expert Mod 2.5K+
P: 2,532
So, that is officailly called, in Access-speak, "fat-fingering", which often happens when we, ourselves, don't have your database to play with.

Let me take a look at the SQL we created and see if I can't find that extra ")".
Jul 5 '18 #27

twinnyfo
Expert Mod 2.5K+
P: 2,532
Line 38:
Expand|Select|Wrap|Line Numbers
  1.         "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
Line 63:
Expand|Select|Wrap|Line Numbers
  1.         "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 "
Let me know how that works for you!
Jul 5 '18 #28

NeoPa
Expert Mod 15k+
P: 30,996
This is why it's so important when building SQL using VBA to put it into a string and use Dubug.Print on that string while you're working on it. Twinny does this in line #68 - showing he's at least journeyman level (If not expert) - so you're well covered.

Posting the value from line #68 would have been the better idea, but I can assure you he found and fixed the problem anyway ;-) Working remotely on another's database is always very difficult as you can't see the info you need directly.
Jul 5 '18 #29

P: 13
AHA! I should have caught that one. However, now we've got:

Run-time error 3075:


Syntax error (missing operator) in query expression '(Count(join_ParttoTag.PartID)=1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag.PartID, Count(join_ParttoTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag.PartID IN ()) "GROUP BY join_ParttoTag.PartID HAVING (Count(join_ParttoTag.PartID)=1) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartID) ON Cat1.PartID = tbl_Parts.PartID;
Jul 5 '18 #30

twinnyfo
Expert Mod 2.5K+
P: 2,532
NeoPa:
Twinny does this in line #68 - showing he's at least journeyman level (If not expert) - so you're well covered.
As a result of many failed SQL strings! Definitely a necessity when building complex strings in VBA. Sometimes I even forget to remove the Debug.Print after I get it wo work....
Jul 5 '18 #31

twinnyfo
Expert Mod 2.5K+
P: 2,532
Could you copy and paste the value of the strSQL from your Immediate window?

This would be very helpful. Ctrl-G will bring up that window if it is not already visible.
Jul 5 '18 #32

P: 13
SELECT tbl_Parts.PartID FROM (SELECT join_ParttoTag.PartID, Count(join_ParttoTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag.PartID IN (10)) GROUP BY join_ParttoTag.PartID HAVING (Count(join_ParttoTag.PartID)=1) AS Cat1 INNER JOIN ((SELECT join_ParttoTag.PartID, Count(join_ParttoTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag.PartID IN ()) GROUP BY join_ParttoTag.PartID HAVING (Count(join_ParttoTag.PartID)=0) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartID) ON Cat1.PartID = tbl_Parts.PartID;
Jul 5 '18 #33

NeoPa
Expert Mod 15k+
P: 30,996
@Twinny.

The more complicated a string is to create, and by that I mean mainly SQL strings but also some other types, and I also mean those with many replaceable values such as you might find in a complicated WHERE clause, the more I rely on using Replace() rather than appending various string literals to other values to make up a long and complicated string. Actually, I even have a special Function called MultiReplace() that I use to handle multiple replacement values.

It takes a little getting used to at first but it does have the benefit that you can see the structure of the main string directly from the start. Personally, I use the percent (%) character to flag replaceable items within the master string so they're easy to spot and it's easy to understand that they're to be replaced.

So, for example, a SQL string selecting values based on a particular date as well as a particular client ID (Alpha) and operator ID (Numeric) could be either of :
  1. Expand|Select|Wrap|Line Numbers
    1. strSQL = "SELECT *" & VbNewLine & "FROM [tblTransaction]" & VbNewLine _
    2.        & "WHERE  ([TranDate]=#" & Format(datTran, "yyyy\-m\-d") & "#)" _
    3.        & VbNewLine & "  AND  ([ClientID]='" & strClientID & "')" & VbNewLine _
    4.        & "  AND  ([OperatorID]=" & lngOperatorID & ")"
  2. Expand|Select|Wrap|Line Numbers
    1. strSQL = MultiReplace("SELECT *%L" _
    2.                     & "FROM   [tblTransaction]%L" _
    3.                     & "WHERE  ([TranDate]=#%D#)%L" _
    4.                     & "  AND  ([ClientID]='%C')%L" _
    5.                     & "  AND  ([OperatorID]=%O)" _
    6.                     , "%D", Format(datTran, "yyyy\-m-\d") _
    7.                     , "%C", strClientID _
    8.                     , "%O", lngOperatorID _
    9.                     , "%L", VbNewLine)
It may be that it takes a short while to get your head around the different way of working, but believe me it works much more easily and reliably once you have. You'll see immediately though, that it's so much easier to spot typos and unmatched parentheses this way.

By the way, my code for MultiReplace() is included here for fullness.
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
  2. '  first with the second wherever found in strMain.
  3. 'Using VbBinaryCompare means that case is recognised and not ignored.
  4. '08/05/2013 Updated to support passing of an array directly into avarArgs.
  5. Public Function MultiReplace(ByRef strMain As String _
  6.                            , ParamArray avarArgs() As Variant) As String
  7.     Dim intX As Integer
  8.     Dim avarVals() As Variant
  9.  
  10.     'Code to handle avarArgs passed as an existing array.
  11.     If (UBound(avarArgs) = LBound(avarArgs)) _
  12.     And IsArray(avarArgs(LBound(avarArgs))) Then
  13.         ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
  14.         For intX = LBound(avarVals) To UBound(avarVals)
  15.             avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
  16.         Next intX
  17.     Else
  18.         avarVals = avarArgs
  19.     End If
  20.     If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
  21.     MultiReplace = strMain
  22.     For intX = LBound(avarVals) To UBound(avarVals) Step 2
  23.         MultiReplace = Replace(Expression:=MultiReplace, _
  24.                                Find:=Nz(avarVals(intX), ""), _
  25.                                Replace:=Nz(avarVals(intX + 1), ""), _
  26.                                Compare:=vbBinaryCompare)
  27.     Next intX
  28. End Function
Jul 5 '18 #34

twinnyfo
Expert Mod 2.5K+
P: 2,532
If my count of Parentheses is correct, and my analysis of the sub-query is also accurate, Lines 61-63 should be:

Expand|Select|Wrap|Line Numbers
  1.         "IN (" & strMYs & ") " & _
  2.         "GROUP BY join_ParttoTag.PartID " & _
  3.         "HAVING (Count(join_ParttoTag.PartID)=" & intX & ")) AS Cat2 " & _
Jul 5 '18 #35

NeoPa
Expert Mod 15k+
P: 30,996
NB. Notice how I will always try to format the SQL string such that when it's printed out it's easily and quickly interpretable by the viewer. Unformatted SQL strings, especially the very long and complicated ones, becomes exponentially more difficult to interpret. No-one wastes their time when they format SQL strings to view or post - and expecially not to work on.
Jul 5 '18 #36

twinnyfo
Expert Mod 2.5K+
P: 2,532
@NeoPa,

I remember the first time I saw you using your "%Arg" technique, and at first it set me back, but once I was able to wrap my head around what you were doing it made sense. Now that I understand the "why" it makes even more sense.

It may take me some time to digest this one, but I've been know to chew on my cud for a while when it comes to these little tricks of the trade.

Thanks for the Code--already adding it to my master set of funky-functions!
Jul 5 '18 #37

NeoPa
Expert Mod 15k+
P: 30,996
Twinny:
If my count of Parentheses is correct, ...
It isn't. Sorry.

It's only line #3 that's relevant and it has an extra closing parenthesis.
Jul 5 '18 #38

NeoPa
Expert Mod 15k+
P: 30,996
Twinny:
Thanks for the Code--already adding it to my master set of funky-functions!
My pleasure.

You don't need me to explain how much fun it is to share with a willing learner. Keep up the good work :-)
Jul 5 '18 #39

twinnyfo
Expert Mod 2.5K+
P: 2,532
Ah, yes! Line 3. However, line 6 and 7 from the displayed SQL will be fixed with the adjustment in post #35. It needs to have the sub-query as a complete query.
Jul 5 '18 #40

NeoPa
Expert Mod 15k+
P: 30,996
Ah. My bad. I assumed that was a Field name rather than SubQuery name. As the latter you have it quite right, of course.

I should have guessed. That would have been far too sloppy for you. A big clue yet I missed it.
Jul 5 '18 #41

P: 13
I wanted to thank you all for your help. I am much further along in my understanding of SQL.

I also wanted to give an update. I decided to try another route using recordset filters instead, and it is working. I don't know that it is the most elegant solution, but it's providing the results I want.

Thanks!!!!
Jul 9 '18 #42

twinnyfo
Expert Mod 2.5K+
P: 2,532
We are glad you found a working solution.

Would you mind posting your solution so others may benefit from your success?
Jul 9 '18 #43

P: 13
I'm still working on all the error handling, but I will once I get it solid.
Jul 9 '18 #44

Post your reply

Sign in to post your reply or Sign up for a free account.