parse and reformat a string in VBA

I'm getting a string from a text box in a form and need to substitute some values with other values, and take the remainder of the string contents and reformat them.

An example of what I'm getting from the text box would be something like:

DEQ;DEP;QN:BN

What I want at the end would look something like this:

AND TableName.ColName like ‘*DEQ*’ AND TableName.ColName like ‘*DEP*” AND TableName.ColName like ‘*QN*’ OR TableName.ColName like ‘*BN*’
where the semicolon is substituted for AND and the colon is substituted for OR.

Some previous code I’ve used (when I was parsing just the semicolon looks like this:
    If Nz(Me.TxtAttribute5) <> "" Then
        spartse = Split(Me.TxtAttribute5, ";")
        For e = LBound(spartse) To UBound(spartse)
             strLste = strLste & " AND " & "TBQM_Basis.Desc Like '*" & spartse(e) & "*'"
        Next e
        strWhere = strWhere & strLste
    End If

What has me stumped is parsing for a second condition (or for no conditions). I’ve tried other nested if statements and also get that I probably need to be looking for the semicolon and the colon at the same time (otherwise, whichever you don’t search for first becomes part of the string) but think there is probably a more elegant way to approach the problem, so I’m putting this out there.
alfamikefoxtrotAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
I'd use Regular Expressions for it, as explained in my article here.

1) Add this to a regular VBA module:

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.Test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Open in new window


2) Now, change your code block to this:

    If RegExpFind(Nz(Me.TxtAttribute5, ""), "^[^:;]+([:;][^:;]+)*", 1) <> "" Then
        spartse = RegExpFind(Me.TxtAttribute5, "(^[^:;]+)|([:;][^:;]+)")
        strLste = "TBQM_Basis.Desc Like '*" & spartse(0) & "*'"
        For e = 1 To UBound(spartse)
             strLste = strLste & IFF(Left(spartse(e), 1) = ";", " AND ", " OR ") & _
                "TBQM_Basis.Desc Like '*" & Mid(spartse(e), 2) & "*'"
        Next e
        strWhere = strWhere & strLste
    End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Not sure where you are going with this but parsing this string is only the beginning of the problem.  Given your example, Boolean logic says to interpret the complex condition as:

Where (fldA LIKE "*DEQ*" AND fldB LIKE "*DEP*" AND fldC LIKE "*QN*") OR fldD LIKE "*BN*"

which is quite different from:
Where (fldA LIKE "*DEQ*" AND fldB LIKE "*DEP*") AND (fldC LIKE "*QN*" OR fldD LIKE "*BN*")
or
Where fldA LIKE "*DEQ*" AND ((fldB LIKE "*DEP*" AND fldC LIKE "*QN*") OR fldD LIKE "*BN*")


So either the first three conditions ALL need to be true OR the fourth condition needs to be true.  But if you meant either of the other two interpretations or even something else, you will not get the result you anticipated.

When you combine AND/OR/NOT in a complex expression, you will almost certainly need to use parenthesis to specify the order of precedence.   Not to mention the fact that using LIKE this will force the query engine to do full table scans so I hope you don't have a lot of rows.  And then there is the problem of column names which your question doesn't address.
Gustav BrockCIOCommented:
You could do like this:

    strLste = " AND TableName.ColName like ‘*{0}*’ AND TableName.ColName like ‘*{1}*” AND TableName.ColName like ‘*{2}*’ OR TableName.ColName like ‘*{3}*’"

    If Nz(Me.TxtAttribute5) <> "" Then
         spartse = Split(Me.TxtAttribute5, ";")
         For e = LBound(spartse) To UBound(spartse)
              strLste = Replace(strLste, "{" & CStr(e) & "}", spartse(e))
         Next e
         strWhere = strWhere & strLste
    End If

Open in new window

Of course, if UBound will not be constant, you will have to refine this method.

/gustav
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

alfamikefoxtrotAuthor Commented:
Patrick -- created the module and pasted the code as you recommended. The regular function approach definitely strikes me as the right way to go, but unfortunately I keep getting a compile error -- the code breaks at the snippet and the compile error states "procedure or variable expected, not module." I tried making the module a public function and also tried storing as a procedure instead of a module and still got a compile error (but a different one). Module and function are both named RegExpFind. I'm using the code to generate a filter statement.

I don't readily see any code errors.  Any thoughts?
alfamikefoxtrotAuthor Commented:
Patrick -- fixed the above issue (recreated module and resaved) but have two more observations -- I changed the iff to iif in the snippet which lets the other code run, but now get a type mismatch at        

spartse = RegExpFind(Me.TxtAttribute5, "(^[^:;]+)|([:;][^:;]+)") if Me.TxtAttribute5 is populated.

FYI, I define spartse() As String.
alfamikefoxtrotAuthor Commented:
Patrick -- accepting your solution -- fixed one more minor issue with the code string (needed by the filter) and converted spartse() to a variant. All works fine.
alfamikefoxtrotAuthor Commented:
Function was great, but needed some minor additional debugging in snippet to work. Quick assistance was really appreciated.
Patrick MatthewsCommented:
Glad to help, and sorry about that "IFF" vs "IIF" typo :)
PatHartmanCommented:
@alfamikefoxtrot
Could you at least acknowledge that you don't care about Boolean logic so I know you understood what I was trying to tell you about combining AND and OR in the same expression.
alfamikefoxtrotAuthor Commented:
Pat Hartman -- that, frankly, is a pretty rude comment. I will state the following: 1) the string I'm looking for is a part of a larger SQL string that has other parsing that goes with it that you didn't see and 2) your earlier comments really didn't help at all and did not provide any type of technical solution. You are the expert -- I'm looking to you to provide me first with some insight to the specific issue I need to have solved, and whether you think my approach is correct or not. If the latter, then I'd look for something that would constructively help me -- what you provided me with was more tearing down than building up. I don't appreciate the tone.
PatHartmanCommented:
I'm sorry I offended you.  I was also offended, hence the attitude.  Patrick seemed to have answered the technical part of the question so until you tried that, there was no need for me to suggest another method.  However, not everyone understands Boolean logic and I wanted to make sure you knew what you were doing.   If you hadn't gotten to that part of the process yet, I wanted you to stop and think about it now before wasting your time since creating this type of string is more complex than your simple example implies.   As you can imagine, not everyone who asks questions here has a technical background and frequently they do things without understanding the ramifications.  I was attempting to get you to think about the bigger picture in case you hadn't already.

My original post did not question what you were doing.  It only said that I didn't know what you were doing and that was because you didn't tell us.  It is not always necessary that we know what you are doing but sometimes it helps.  Frequently people decide on a solution based on inadequate knowledge and ask how to implement it when they should be explaining their objective and asking for a solution and then help to implement it if they need it.  Experts have to be able to read between the lines and go beyond simply telling you how to take the safety off the trigger.  Way too many experts simply type "try this" without any explanation of why what you were doing was wrong or any thought that you might be doing the wrong thing entirely.
alfamikefoxtrotAuthor Commented:
Fair point -- and I could also have been more explicit with what I shared. This is one of those cases, I believe, where I know so much about the specific problem I'm working on that sharing additional information actually didn't occur to me. In this case, I had some logic farther below that provides more to the SQL statement to make sure the AND/OR problem doesn't crop up and provide a weird dataset-- but you're right -- standing on it's own, it would not have worked. I do appreciate your attempt to help to see the bigger picture.
PatHartmanCommented:
Thanks.  Good luck with the project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.