Multiple search criteria on one junction table field

[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.PartI D FROM (SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN (10)) GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 1) AS Cat1 INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 0) AS Cat2 INNER JOIN tbl_Parts ON Cat2 = tbl_Parts.PartI D) ON Cat1.PartID = tbl_Parts.PartI D;

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