Solved

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

Posted on 2014-02-26
2
455 Views
Last Modified: 2014-02-26
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
Comment
Question by:terpsichore
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39888582
Worked fine here:

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

Jim.
0
 

Author Closing Comment

by:terpsichore
ID: 39888594
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question