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

terpsichore
terpsichore used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Worked fine here:

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

Jim.

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial