RgExp.Pattern (1 Viewer)

hardy1976

Still learning...
Local time
Today, 10:54
Joined
Apr 27, 2006
Messages
200
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

I’m here to help
Staff member
Local time
Today, 10:54
Joined
Oct 29, 2018
Messages
21,358
Hi. I'm no expert on Regular Expressions but I am guessing the first of that expression is probably correct. This part:
Code:
^\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

I’m here to help
Staff member
Local time
Today, 10:54
Joined
Oct 29, 2018
Messages
21,358
Hi. This might work.
Code:
\d{11}\/\d{8}|\d{11}\/[A-Za-z]{4}\d{4}
Hope it helps...
 

hardy1976

Still learning...
Local time
Today, 10:54
Joined
Apr 27, 2006
Messages
200
Don't know why these are not working - do only some regex expressions work in Access?
 

hardy1976

Still learning...
Local time
Today, 10:54
Joined
Apr 27, 2006
Messages
200
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

I’m here to help
Staff member
Local time
Today, 10:54
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, could you try this code but use the pattern I gave you earlier? Thanks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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?

Code:
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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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

I’m here to help
Staff member
Local time
Today, 10:54
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
Both my last pattern and theDBguy's come back as true for both the sample strings?

Code:
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

Ignore List Poster Boy
Local time
Today, 13:54
Joined
Apr 27, 2015
Messages
6,286
Hi Paul. It worked for me too. Guess my Validater isn't as good as I thought!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:54
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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.
 

Users who are viewing this thread

Top Bottom