CODEWITHSUNDEEP
ms-accessms-access-2010
Edgar
Edgar

Reputation: 5

Query to parse a field and display it

I have a table with values

Errors:
X_11;SR_4;D_11;SR_2
SR_4;T_22
E_18; E_28; SR_3; 
E_28; SR_3; 
SR_2;SR_4

I need to put in a query to parse the values so that anything with SR comes up so I do like "*SR*" but in the output I need to display only this:

Errors:
SR_4;SR_2
SR_4
SR_3 
SR_3 
SR_2;SR_4

I would like this in query with many fields other than this one ... instead of VBA. I am using MS Access 2010, I am guessing some type of parsing with each field being separated with ";" that will only capture SR ones?

Upvotes: 0

Views: 83

Answers (2)

HansUp
HansUp

Reputation: 97111

I think you can get what you need by splitting your input string into an array and then using the Filter function to create a second array which includes only the SR_ matches from the first array. Finally Join the second array to produce your output string which contains the matches.

Public Function filterString(ByVal pInput As String) As String
    Dim array1() As String
    Dim array2() As String
    array1 = Split(Replace(pInput, " ", vbNullString), ";")
    array2 = Filter(array1, "SR_")
    filterString = Join(array2, ";")
End Function

Compared to a regular expression approach, this function is more concise. I find the logic simpler. And it does not require setting a reference.

Notice also it will accommodate SR codes which include more than a single digit (in case that eventually becomes a requirement). For example:

? filterString("X_11;SR_4;D_11;SR_234")
SR_4;SR_234

You could use that function in a query in the same way @Barranka suggested:

SELECT filterString(y.Errors) AS sr_codes
FROM [yourTable] AS y
WHERE y.Errors Like '*sr*';

Upvotes: 1

Barranka
Barranka

Reputation: 21047

I think regular expressions might be a way to go.

In VBA, you need to enable the reference to "Microsoft VBScript Regular Expressions 5.5". This question and its accepted answer has a detailed descrpition on what are Regular Expressions and how to enable them in your project (it's for Excel, but for Access is the same route).

Once you have the reference enabled, this little function will give you a "clean" string:

Public Function filterString(str As String)
    Dim re As RegExp, obj As Object, x As Variant, first As Boolean
    Set re = New RegExp
    With re
        .Global = True
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "SR_[0-9]" ' This will match the string "SR_" 
                              ' followed by a digit
    End With

    filterString = ""
    first = True

    If re.Test(str) Then
        Set obj = re.Execute(str)
        For Each x In obj
            If first Then
                first = False
            Else
                filterString = filterString & ";"
            End If
            filterString = filterString & x
        Next x
    End If
End Function

If you test it you'll see that the result is:

filterString("X_11;SR_4;D_11;SR_2")
  SR_4;SR_2

which is the result you want.

Now, a simple select query will give you what you need:

select filterString([Errors]) as err
from [yourTable]
where [yourTable].[Errors] like '*sr*'

Hope this helps

Upvotes: 1

Related Questions