Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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