CODEWITHSUNDEEP
sqlregexvbams-access
fresh
fresh

Reputation: 140

Access 2010 Only returning first result Regular Expression result from MatchCollection

I am running a query using a regular expression function on a field where a row may contain one or more matches but I cannot get Access to return any matches except either the first one of the collection or the last one (appears random to me).

Sample Data:

      tbl_1 (queried table)
row_1 abc1234567890 some text
row_2 abc1234567890 abc3459998887 some text
row_3 abc9991234567 abc8883456789 abc7778888664 some text

      tbl_2 (currently returned results)
row_1 abc1234567890
row_2 abc1234567890
row_3 abc7778888664

      tbl_2 (ideal returned results)
row_1 abc1234567890
row_2 abc1234567890
row_3 abc3459998887
row_4 abc9991234567
row_5 abc8883456789
row_6 abc7778888664

Here is my Access VBA code:

Public Function OrderMatch(field As String)

Dim regx As New RegExp
Dim foundMatches As MatchCollection
Dim foundMatch As match

regx.IgnoreCase = True
regx.Global = True
regx.Multiline = True
regx.Pattern = "\b[A-Za-z]{2,3}\d{10,12}\b"

Set foundMatches = regx.Execute(field)

If regx.Test(field) Then
    For Each foundMatch In foundMatches
        OrderMatch = foundMatch.Value
    Next
End If

End Function

My SQL code:

SELECT OrderMatch([tbl_1]![Field1]) AS Order INTO tbl_2
FROM tbl_1
WHERE OrderMatch([tbl_1]![Field1])<>False;

I'm not sure if I have my regex pattern wrong, my VBA code wrong, or my SQL code wrong.

Upvotes: 1

Views: 527

Answers (3)

HansUp
HansUp

Reputation: 97131

Seems you intend to split out multiple text matches from a field in tbl_1 and store each of those matches as a separate row in tbl_2. Doing that with an Access query is not easy. Consider a VBA procedure instead. Using your sample data in Access 2007, this procedure stores what you asked for in tbl_2 (in a text field named Order).

Public Sub ParseAndStoreOrders()
    Dim rsSrc As DAO.Recordset
    Dim rsDst As DAO.Recordset
    Dim db As DAO.database
    Dim regx As Object ' RegExp
    Dim foundMatches As Object ' MatchCollection
    Dim foundMatch As Object ' Match

    Set regx = CreateObject("VBScript.RegExp")
    regx.IgnoreCase = True
    regx.Global = True
    regx.Multiline = True
    regx.pattern = "\b[a-z]{2,3}\d{10,12}\b"

    Set db = CurrentDb
    Set rsSrc = db.OpenRecordset("tbl_1", dbOpenSnapshot)
    Set rsDst = db.OpenRecordset("tbl_2", dbOpenTable, dbAppendOnly)
    With rsSrc
        Do While Not .EOF
            If regx.Test(!field1) Then
                Set foundMatches = regx.Execute(!field1)
                For Each foundMatch In foundMatches
                    rsDst.AddNew
                    rsDst!Order = foundMatch.value
                    rsDst.Update
                Next
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rsSrc = Nothing
    rsDst.Close
    Set rsDst = Nothing
    Set db = Nothing
    Set foundMatch = Nothing
    Set foundMatches = Nothing
    Set regx = Nothing
End Sub

Paste the code into a standard code module. Then position the cursor within the body of the procedure and press F5 to run it.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112632

This loop

For Each foundMatch In foundMatches
    OrderMatch = foundMatch.Value
Next

assigns all the results to the same variable OrderMatch in turn, which always replaces the old value of OrderMatch. Therefore the function will always return the last match.

If you want to return all the values, return an array for instance

Public Function OrderMatch(field As String) As String()
    Dim results() As String
    Dim i As Long

    ... get matches

    ReDim results(0 To foundMatches.Count - 1) As String

    If regx.test(field) Then
        For i = 0 To foundMatches.Count - 1
            results(i) = foundMatches(i).Value
        Next
    End If
    OrderMatch = results
End Function

(I am currently working with Access XP, so I don't know whether the indexes go from 1 to Count or from 0 to Count-1.)

UPDATE

And always specify the return type of functions. This is more informative for people who want to use the function (including you if you have to resuse this function in 6 months) and prevents from supid coding errors. If really Variant is meant, specify ... As Variant so that your intention becomes clear.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53653

This function is only returning one value because that's the way you have set it up with the logic. This will always return the last matching value. 

For Each foundMatch In foundMatches
    OrderMatch = foundMatch.Value
Next

Even though your function implicitly returns a Variant data type, it's not returning an array because you're not assigning values to an array. Assuming there are 2+ matches, the assignment statement OrderMatch = foundMatch.Value inside the loop will overwrite the first match with the second, the second with the third, etc.

Assuming you want to return an array of matching values:

Dim matchVals() as Variant
Dim m as Long

For Each foundMatch In foundMatches
    matchValues(m) = foundMatch.Value
    m = m + 1
    ReDim Preserve matchValues(m)
Next

OrderMatch = matchValues

Upvotes: 0

Related Questions