Access VBA - rs.findfirst using regex validation

Question

Hi all first time post.

I'm running a database that has to check data once imported in. I already use one findfirst that works fine which is just checking length of a string. Next I wanted to find any that didn't match a certain pattern in the same field.

Record set is simply

strSQL = "SELECT * FROM TblOrder ORDER BY ID"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

The first find that works is;

rs.FindFirst ("Len(string) > 13")
    If rs.NoMatch Then
        MsgBox "All good"
    Else
        Do While Not rs.NoMatch
            MsgBox "String over 13 - " & rs.AbsolutePosition + 1
            rs.FindNext ("Len(string) > 13")
        Loop
            rs.FindNext ("Len(string) > 13")
        End If

So that one is no problem but the one below is where I can't get it to work;

rs.FindFirst ("Validation(string, ""123\d{10}$"") = False") 
    If rs.NoMatch Then
        MsgBox "All are correct pattern"
    Else
        Do While Not rs.NoMatch
            rs.FindNext (Validation(string, "123\d{10}$") = False)
            MsgBox "Must start with 978 - " & rs.AbsolutePosition
        Loop
            rs.FindNext (Validation(string, "123\d{10}$") = False)
        End If

I've tried without quotes like on the findnext lines and with as per the findfirst. Without finds null value for the string and with gets a data type mismatch. The string is a number like 123456789 but stored in table as text. The validation works fine if I look through the recordset one row at a time like below;

If Validation(rs!string, "978\d{10}$") = False Then

but I want to scan the entire column rather than go line by line. Line by line was taking too long.

The validation function code is below; Function Validation(ByRef StrInput As String, ByVal strPattern As String) As String

    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")

    With regex

        .Pattern = strPattern
        .IgnoreCase = True
        .Global = False
        .MultiLine = False
    End With


        If regex.Test(StrInput) = True Then
        Validation = True

        Else
        Validation = False

        End If



End Function

Jaehaerys68 Jaehaerys68 · Accepted Answer · 2017-07-20T12:24:20

0

votes

Don't worry about this I've fixed it. False needed to be in speech-marks.

Access VBA - rs.findfirst using regex validation

1 Answers