Multiple search criteria on one junction table field

[INJacqui]

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.

[PhilOfWalton]

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

[twinnyfo]

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.

[Rabbit]

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.

[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.

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.

' Build Engine Type Filter
    If strEngineType = "" Then
        intLoop = 0
        intCount = 0
        For intLoop = 0 To Me.lstEngineType.ListCount - 1
            If Me.lstEngineType.Selected(intLoop) = True Then
                intCount = intCount + 1
            End If
        Next intLoop
 
        If intCount > 0 Then
            intET = 0
            For intET = 0 To Me.lstEngineType.ListCount - 1
                If Me.lstEngineType.Selected(intET) = True Then
                    strEngineType = strEngineType & "([TagID] = " & Me.lstEngineType.Column(0, intET) & ") Or "
                End If
            Next intET
            lngLen = Len(strEngineType) - 4
            strEngineType = Left$(strEngineType, lngLen)
            Me.txtEngineType = strEngineType
        End If
    End If
 

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!

[twinnyfo]

I was thinking something more along the lines of this:

Option Compare Database
Option Explicit
 
Private strFilter As String
 
Private Sub lstEngineType_AfterUpdate()
On Error GoTo EH
 
    strFilter = ""
 
    CheckListBoxes
 
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
EH:
    MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub
 
Private Sub CheckListBoxes()
On Error GoTo EH
 
    BuildFilter Me.Form.Name, "lstEngineType"
    BuildFilter Me.Form.Name, "lstOtherListBox"
    BuildFilter Me.Form.Name, "lstAnotherListBox"
    BuildFilter Me.Form.Name, "lstDifferentListBox"
 
    Exit Sub
EH:
    MsgBox "There was an error checking the list boxes!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub
 
Private Sub BuildFilter(FormName As String, ControlName As String)
On Error GoTo EH
    Dim frm     As Form
    Dim ctl     As Control
    Dim varItem As Variant
 
    Set frm = Forms!(FormName)
    Set ctl = frm!(ControlName)
    For Each varItem In ctl.ItemsSelected
        If strFilter = "" Then
            strFilter = "([TagID] = " & ctl.ItemData(varItem) & ") "
        Else
            strFilter = " OR ([TagID] = " & ctl.ItemData(varItem) & ") "
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing
 
    Exit Sub
EH:
    MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
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!

[NeoPa]

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.

[twinnyfo]

@ NeoPa and Rabbit:

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

Option Compare Database
Option Explicit
 
Private strFilter As String
 
Private Sub lstTagCategory1_AfterUpdate()
On Error GoTo EH
 
    strFilter = ""
 
    CheckListBoxes
 
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
EH:
    MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub
 
Private Sub lstTagCategory2_AfterUpdate()
On Error GoTo EH
 
    strFilter = ""
 
    CheckListBoxes
 
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
EH:
    MsgBox "There was an error with the list box!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub
 
Private Sub CheckListBoxes()
On Error GoTo EH
 
On Error GoTo EH
    Dim frm     As Form
    Dim ctl     As Control
    Dim varItem As Variant
    Dim strCats As String
    Dim intX    As Integer
    Dim strSQL  As String
 
    Set frm = Forms!(FormName)
    Set ctl = frm!lstTagCategory1
    intX = 0
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strCats = "" Then
            strCats = ctl.ItemData(varItem)
        Else
            strCats = ", " & ctl.ItemData(varItem)
        End If
    Next varItem
 
    strSQL = "SELECT tbl_Parts.PartID " & _
        "FROM (SELECT join_PartsToTags.PartID, " & _
        "Count(join_PartsToTags.PartID) " & _
        "FROM join_PartsToTags " & _
        "WHERE join_PartsToTags.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_PartsToTags.PartID " & _
        "HAVING Count(join_PartsToTags.PartID)=" & intX & ") AS Cat1 "
 
    Set ctl = frm!lstTagCategory2
    intX = 0
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strFilter = "" Then
            strFilter = ctl.ItemData(varItem)
        Else
            strFilter = ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing
 
    strSQL = strSQL & _
        "INNER JOIN " & _
        "((SELECT join_PartsToTags.PartID, " & _
        "Count(join_PartsToTags.PartID) " & _
        "FROM join_PartsToTags " & _
        "WHERE join_PartsToTags.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_PartsToTags.PartID " & _
        "HAVING Count(join_PartsToTags.PartID)=" & intX & ") AS Cat2 " & _
        "INNER JOIN tbl_Parts " & _
        "ON Cat2 = tbl_Parts.PartID) " & _
        "ON Cat1.PartID = tbl_Parts.PartID;"
 
    strFilter = "PartID IN (" & strSQL & ")"
 
    Exit Sub
EH:
    MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
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.

[Rabbit]

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.

[twinnyfo]

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

[Rabbit]

@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.

[INJacqui]

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

[NeoPa]

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.

[twinnyfo]

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.

[INJacqui]

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?

[INJacqui]

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.

[Rabbit]

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.

[INJacqui]

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

[twinnyfo]

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.

[NeoPa]

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.

[Rabbit]

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.

[INJacqui]

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.

Option Compare Database
Option Explicit
 
Private strFilter As String
 
Private Sub lstEngineType_AfterUpdate()
On Error GoTo EH
 
    strFilter = ""
 
    CheckListBoxes
 
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
 
EH:
    MsgBox "There was an error with the list box."
    Exit Sub
 
End Sub
 
Private Sub lstModelYear_AfterUpdate()
On Error GoTo EH
 
    strFilter = ""
 
    CheckListBoxes
 
    With Me
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
 
EH:
    MsgBox "There was an error with the list box."
    Exit Sub
 
End Sub
 
 
Private Sub CheckListBoxes()
On Error GoTo EH
 
    Dim db As Database
    Dim rsResults As Recordset
 
    Dim frm As Form
    Dim ctl As Control
    Dim varItem As Variant
    Dim strCats As String
    Dim strCats2 As String
    Dim intX As Integer
    Dim strSQL As String
    Dim strSQL_ET As String
    Dim strSQL_MY
 
 
 
' Engine Type
    Set frm = Forms!frm_BuildCart_Bytes2
    Set ctl = frm!lstEngineType
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strCats = "" Then
            strCats = ctl.ItemData(varItem)
        Else
            strCats = strCats & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
 
    strSQL = "SELECT tbl_Parts.PartID " & _
        "FROM (SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
 
' Model Year
    Set ctl = frm!lstModelYear
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strFilter = "" Then
            strFilter = ctl.ItemData(varItem)
        Else
            strFilter = strFilter & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing
 
 
    strSQL = strSQL & _
        "INNER JOIN " & _
        "((SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strCats & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
        "INNER JOIN tbl_Parts " & _
        "ON Cat2 = tbl_Parts.PartID) " & _
        "ON Cat1.PartID = tbl_Parts.PartID;"
 
        strFilter = "PartID IN (" & strSQL & ")"
 
    Exit Sub
 
EH:
    MsgBox "There was an error building the filter."
    Exit Sub
 
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!

[twinnyfo]

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.

Private Sub CheckListBoxes()
On Error GoTo EH
    Dim strETs      As String
    Dim strMYs      As String
    Dim frm         As Form
    Dim ctl         As Control
    Dim intX        As Integer
    Dim varItem     As Variant
    Dim strSQL      As String
    Dim db          As DAO.Database
    Dim rst         As DAO.Recordset
    Dim strPartIDs  As String
 
    strETs = ""
    strMYs = ""
 
' Engine Type
    Set frm = Forms!frm_BuildCart_Bytes2
    Set ctl = frm!lstEngineType
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strETs = "" Then
            strETs = ctl.ItemData(varItem)
        Else
            strETs = strETs & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
 
    strSQL = "SELECT tbl_Parts.PartID " & _
        "FROM (SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strETs & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "
 
' Model Year
    Set ctl = frm!lstModelYear
    intX = 0
 
    For Each varItem In ctl.ItemsSelected
        intX = intX + 1
        If strMYs = "" Then
            strMYs = ctl.ItemData(varItem)
        Else
            strMYs = strMYs & ", " & ctl.ItemData(varItem)
        End If
    Next varItem
    Set ctl = Nothing
    Set frm = Nothing
 
    strSQL = strSQL & _
        "INNER JOIN " & _
        "((SELECT join_ParttoTag.PartID, " & _
        "Count(join_ParttoTag.PartID) " & _
        "FROM join_ParttoTag " & _
        "WHERE join_ParttoTag.PartID " & _
        "IN (" & strMYs & ")) " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 " & _
        "INNER JOIN tbl_Parts " & _
        "ON Cat2 = tbl_Parts.PartID) " & _
        "ON Cat1.PartID = tbl_Parts.PartID;"
 
    Debug.Print strSQL
 
    strFilter = ""
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rst
        If ((Not .BOF) And (Not .EOF)) Then
            .MoveFirst
            Do While Not .EOF
                If strFilter = "" Then
                    strFilter = !PartID
                Else
                    strFilter = strFilter & ", " & !PartID
                End If
                .MoveNext
            Loop
        End If
        .Close
    End With
 
    Debug.Print strFilter
 
    Exit Sub
EH:
    MsgBox "There was an error building the filter!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Hope this hepps!

[INJacqui]

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

[twinnyfo]

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?

[INJacqui]

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!

[twinnyfo]

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 ")".

[twinnyfo]

Line 38:

        "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat1 "

Line 63:

        "HAVING (Count(join_ParttoTag.PartID)=" & intX & ") AS Cat2 "

Let me know how that works for you!

[NeoPa]

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.

[INJacqui]

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;

[twinnyfo]

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

[twinnyfo]

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.

[INJacqui]

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;

[NeoPa]

@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. strSQL = "SELECT *" & VbNewLine & "FROM [tblTransaction]" & VbNewLine _
          & "WHERE  ([TranDate]=#" & Format(datTran, "yyyy\-m\-d") & "#)" _
          & VbNewLine & "  AND  ([ClientID]='" & strClientID & "')" & VbNewLine _
          & "  AND  ([OperatorID]=" & lngOperatorID & ")"

2. strSQL = MultiReplace("SELECT *%L" _
                       & "FROM   [tblTransaction]%L" _
                       & "WHERE  ([TranDate]=#%D#)%L" _
                       & "  AND  ([ClientID]='%C')%L" _
                       & "  AND  ([OperatorID]=%O)" _
                       , "%D", Format(datTran, "yyyy\-m-\d") _
                       , "%C", strClientID _
                       , "%O", lngOperatorID _
                       , "%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.

'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
'  first with the second wherever found in strMain.
'Using VbBinaryCompare means that case is recognised and not ignored.
'08/05/2013 Updated to support passing of an array directly into avarArgs.
Public Function MultiReplace(ByRef strMain As String _
                           , ParamArray avarArgs() As Variant) As String
    Dim intX As Integer
    Dim avarVals() As Variant
 
    'Code to handle avarArgs passed as an existing array.
    If (UBound(avarArgs) = LBound(avarArgs)) _
    And IsArray(avarArgs(LBound(avarArgs))) Then
        ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
        For intX = LBound(avarVals) To UBound(avarVals)
            avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
        Next intX
    Else
        avarVals = avarArgs
    End If
    If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
    MultiReplace = strMain
    For intX = LBound(avarVals) To UBound(avarVals) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarVals(intX), ""), _
                               Replace:=Nz(avarVals(intX + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intX
End Function

[twinnyfo]

If my count of Parentheses is correct, and my analysis of the sub-query is also accurate, Lines 61-63 should be:

        "IN (" & strMYs & ") " & _
        "GROUP BY join_ParttoTag.PartID " & _
        "HAVING (Count(join_ParttoTag.PartID)=" & intX & ")) AS Cat2 " & _

[NeoPa]

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.

[twinnyfo]

@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!

[NeoPa]

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.

[NeoPa]

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

[twinnyfo]

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.

[NeoPa]

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.

[INJacqui]

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!!!!

[twinnyfo]

We are glad you found a working solution.

Would you mind posting your solution so others may benefit from your success?

[INJacqui]

I'm still working on all the error handling, but I will once I get it solid.