Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

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

0
terpsichore
Asked:
terpsichore
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now