set custom filter - comma and hyphen delimited input - need code

Dear experts -
I have an input box, where users can input a series of comma or hyphen-delimited numbers. This then sets a filter on a subform.
Example of inputs:
1) 1,2,3 [will limit results to parts 1, 2 and 3]
2) 1-4 (will limit results to parts 1,2,3 and 4)

We are using the function below, but suddenly it choked, when a user entered:
3-6,8
[for which they wanted to limit results to parts 3,4,5,6,8]

Does anyone have a function that does this?

Thanks!

Public Function CustomPartFilter(ByVal parmFilter As String, ByVal parmKeyFieldname As String) As String
    Dim strCondition As String
    Dim stringout As String
    Dim strParsed() As String
    Dim strInVals As String
    Dim strBetweenVals As String
    Dim lngLoop As Long
    Dim I As Long

    Dim gdChar As String
    gdChar = "0123456789-,"
    stringout = ""
    
    For I = 1 To Len(parmFilter)
        If InStr(gdChar, Mid(parmFilter, I, 1)) > 0 Then stringout = stringout & Mid$(parmFilter, I, 1)
    Next

    
    strParsed = Split(stringout, ",")
    For lngLoop = 0 To UBound(strParsed)
        If InStr(strParsed(lngLoop), "-") = 0 Then
            strInVals = strInVals & "," & strParsed(lngLoop)
        Else
            strBetweenVals = strBetweenVals & IIf(strBetweenVals = "", "", " or ") & " (" & parmKeyFieldname & " between " & Split(strParsed(lngLoop), "-")(0) & " and " & Split(strParsed(lngLoop), "-")(1) & ")"
        End If
    Next
    CustomPartFilter = IIf(Nz(strInVals, "") <> "", parmKeyFieldname & " In (" & Mid(strInVals, 2) & ") ", "") & IIf(Nz(strInVals, "") <> "" And Nz(strBetweenVals, "") <> "", " or ", "") & strBetweenVals


End Function

Open in new window

terpsichoreAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Worked fine here:

? CustomPartFilter("3-6,8","Key")
Key In (8)  or  (Key between 3 and 6)

Jim.
0
 
terpsichoreAuthor Commented:
You are right!
You also led me to the solution - the problem was that the SQL string being built did not have parentheses, thus if the function returned something with "OR" in the middle, the logic was all screwed up.
Case solved.
THANKS!
0
All Courses

From novice to tech pro — start learning today.