RgExp.Pattern (1 Viewer)

NauticalGent said:

Try this:

https://www.access-programmers.co.uk/forums/showthread.php?t=297155&highlight=regex

I didnt write the code, but it is what I use when I am testing my patterns. Since it is written in VBA, if I get a successful test, I KNOW it will work...

Click to expand...

hardy1976 said:

Got it thank you all for your help
"^\d{11}\/[a-zA-Z0-9]{4}\d{4}|^\d{11}\/\d{8}$"

Click to expand...

Gasman said:

Well the expressions I posted worked on the test site I linked to?

Plus, what is TestDate doing?

Is your code even doing what you think it should be doing?

Code:Copy to clipboard

Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * from [tablename]"


Set rst = CurrentDb.OpenRecordset(strSQL)

RgExp.Pattern = "^\d{11}\/\d{8}$"
'99900295953/00000001

rst.MoveFirst

Do Until rst.EOF
    If RgExp.Test(Nz(rst!field1, 0)) = True Then
        field1data = Left(rst![field1], 11)
    Else
        If TestDate(Nz(rst![PStart Date], 0)) = "valid" Then
            rst.Edit
            rst!field1 = field1data
            rst.Update
        End If
    End If
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Click to expand...

hardy1976 said:

Sorry missed all the comments on here as I was trying to crack it - APPRECIATE ALL YOUR HELP EVERYONE

Click to expand...

theDBguy said:

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Click to expand...

hardy1976 said:

Got there eventually - using a function and query approach helped loads.

Click to expand...

theDBguy said:

Access is not hard if you have the patience. Cheers!

Click to expand...

hardy1976 said:

I've been trying to solve that for about 3 months on and off! Query approach helped sooooooo much!

Click to expand...