RgExp.Pattern

[hardy1976]

Hi,

HELP - I need a regex expression that will test for...

11 digits then a
"/" then either
8 digits
OR 4 alpha/numeric followed by 4 numeric...

egs
99039002885/AAAA0001
or
99039002885/00000001

I've got this but it doesnt work
= "^\d{11}\/\d{8}$"

Thanks

 

[theDBguy]

Hi. I'm no expert on Regular Expressions but I am guessing the first of that expression is probably correct. This part:

^\d{11}\/

Now, I am thinking since you have an OR situation after that part, you might need a character set enclosed in square brackets []. Just a thought...

 

[theDBguy]

Hi. This might work.

\d{11}\/\d{8}|\d{11}\/[A-Za-z]{4}\d{4}

Hope it helps...

 

[Gasman]

Try

^\d{11}\/[A-Z]{4}\d{4}|\d{8}$

or

^\d{11}\/[A-Z]{4}\d{4}|^\d{11}\/\d{8}$

Found from using
https://regex101.com/
https://www.regular-expressions.info/alternation.html

HTH

 

[hardy1976]

Don't know why these are not working - do only some regex expressions work in Access?

 

[NauticalGent]

Can you show your code...?

 

[hardy1976]

Hi - loosely....

Dim RgExp As Variant
Set RgExp = CreateObject("VBScript.RegExp")
DoCmd.SetWarnings False

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

 

[theDBguy]

Hi. Just curious, could you try this code but use the pattern I gave you earlier? Thanks!

 

[hardy1976]

Hi. Just curious, could you try this code but use the pattern I gave you earlier? Thanks!

Hi - got page could not be displayed... found it no s worked
http://thedbguy.blogspot.com/2016/02/extract-email-address.html

 

[Gasman]

Don't know why these are not working - do only some regex expressions work in Access?

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?

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

 

[NauticalGent]

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

It doesnt work on my tester either. I am no expert on RegEx, but my tester is accurate...

 

[theDBguy]

Hi - got page could not be displayed... found it no s worked
http://thedbguy.blogspot.com/2016/02/extract-email-address.html

Hi. Sorry to hear that. I'll see if I can make a demo later.

 

[NauticalGent]

Hi. Sorry to hear that. I'll see if I can make a demo later.

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...

 

[theDBguy]

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...

Hi John. Thanks! Saves me some time today. Cheers!

 

[Gasman]

Do we even know if it is not working?

If the expression is found Field1data is set and nothing else.?

Or am I missing something obvious?

 

[theDBguy]

Do we even know if it is not working?

If the expression is found Field1data is set and nothing else.?

Or am I missing something obvious?

Hi. I think the problem is we don't have the actual data, so we can't verify it it's working or not.

 

[Gasman]

Both my last pattern and theDBguy's come back as true for both the sample strings?

Sub testRegex()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim RgExp As Variant
Set RgExp = CreateObject("VBScript.RegExp")
strSQL = "99039002885/AAAA0001"
'strSQL = "99039002885/00000001"

RgExp.Pattern = "^\d{11}\/[A-Z]{4}\d{4}|^\d{11}\/\d{8}$"
RgExp.Pattern = "\d{11}\/\d{8}|\d{11}\/[A-Za-z]{4}\d{4}"
'99900295953/00000001
Debug.Print RgExp.Test(strSQL)
End Sub

 

[NauticalGent]

Hi Paul. It worked for me too. Guess my Validater isn't as good as I thought!

 

[theDBguy]

Both my last pattern and theDBguy's come back as true for both the sample strings?

LOL. That's because they're the same, only backwards. Where you had,


Pattern1 OR Pattern2


I had,


Pattern2 OR Pattern1


Cheers!

 

[Gasman]

Hi Paul. It worked for me too. Guess my Validater isn't as good as I thought!

I thought that strange, as the site I worked out the pattern on produced two matches for the strings originally posted.