submatches

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi John,

I've got your code working. However i do not understand some parts of
code. Could you explain me what or where this line of code looks at:

oMatches(0).SubMatches(0)

Because if i try the following string into the code, i get this:

?XXX("33.22.83.436 test")
Acct Number:
Transfercode:
Description: 33.22.83.436 test

Should be:

Acct Number: 33.22.83.436
Transfercode:
Description: test

And in case if there is some text before the acct number:

Acct Number: 33.22.83.436
Transfercode: ATM
Description: test
 
This is because you didn't describe the situation accurately in your
original message. You said:
The accountnr has at most 11 characters (numerical with dots) and the
transfercode has a variable length.
Click to expand...

Now you're complaining because it won't accept a 12-character account number
33.22.83.436
Change
{1,11}
in the pattern to
{1,12}
or the actual minimum and maximum numbers of characters in the account
number.


Similarly, when you said
The first word of the description field can be an accountnumber or
transfercode.
Click to expand...

I assumed that you meant just that, and that all the remaining words in the
description field are just the description. From what you now say it appears
this is not the case, and that there can be both a transfer code and an
account number, or just an account number. Can you explain exactly what the
possibilities are, with examples?
 
Try this. The pattern now recognises

-optional transfercode at start of line (if the first character is a
letter, takes everything from there to the first space or tab)

-optional account number (5 to 12 digits and dots followed by a space or
tab)

-description (everything else).




Public Sub XXX()
Dim strDescription As String

Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection

strDescription = "TCode 123.45.92.1 That was the acct number"

Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?:([a-z][^ ]+)\s+)?(?:([0-9.]{5,12})\s+)?(.*)$"


' ^ anchors to start of string
' (?: ... ) groups terms in the expression
' ([a-z][^ ]+) matches and captures a substring
' starting with a letter and continuing
' with any characters except a space,
' i.e. transfercode
' \s+ matches white space, e.g. spaces or tabs
' ([0-9.]{5,12}) matches and captures 5 to 12 digits
' and dots, i.e. account number
' ? match preceding term 0 or 1 times
' (i.e. cover the case where there's
' no acct number or code)
' (.*)$ match the remainder of the line, i.e.
' the description.

oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.
Set oMatches = oRE.Execute(strDescription) ' Execute search.
If oMatches.Count > 0 Then
On Error Resume Next
Debug.Print "Transfercode: " & oMatches(0).SubMatches(0)
Debug.Print "Acct Number: " & oMatches(0).SubMatches(1)
Debug.Print "Description: " & oMatches(0).SubMatches(2)
Else
Debug.Print "Description: " & strDescription
End If

Set oMatches = Nothing
Set oRE = Nothing

End Sub
 
Hi John,

Thanks for the adjustment. This is exactly want i try to do.

Now how can i update or insert the separated values into a table?

This is the last thing i will ask you.

Greetings,

Jason
John Nurick said:
Try this. The pattern now recognises

-optional transfercode at start of line (if the first character is a
letter, takes everything from there to the first space or tab)

-optional account number (5 to 12 digits and dots followed by a space or
tab)

-description (everything else).




Public Sub XXX()
Dim strDescription As String

Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection

strDescription = "TCode 123.45.92.1 That was the acct number"

Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?:([a-z][^ ]+)\s+)?(?:([0-9.]{5,12})\s+)?(.*)$"


' ^ anchors to start of string
' (?: ... ) groups terms in the expression
' ([a-z][^ ]+) matches and captures a substring
' starting with a letter and continuing
' with any characters except a space,
' i.e. transfercode
' \s+ matches white space, e.g. spaces or tabs
' ([0-9.]{5,12}) matches and captures 5 to 12 digits
' and dots, i.e. account number
' ? match preceding term 0 or 1 times
' (i.e. cover the case where there's
' no acct number or code)
' (.*)$ match the remainder of the line, i.e.
' the description.

oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.
Set oMatches = oRE.Execute(strDescription) ' Execute search.
If oMatches.Count > 0 Then
On Error Resume Next
Debug.Print "Transfercode: " & oMatches(0).SubMatches(0)
Debug.Print "Acct Number: " & oMatches(0).SubMatches(1)
Debug.Print "Description: " & oMatches(0).SubMatches(2)
Else
Debug.Print "Description: " & strDescription
End If

Set oMatches = Nothing
Set oRE = Nothing

End Sub


This is because you didn't describe the situation accurately in your
original message. You said:


Now you're complaining because it won't accept a 12-character account
number
33.22.83.436
Change
{1,11}
in the pattern to
{1,12}
or the actual minimum and maximum numbers of characters in the account
number.


Similarly, when you said


I assumed that you meant just that, and that all the remaining words in
the
description field are just the description. From what you now say it
appears
this is not the case, and that there can be both a transfer code and an
account number, or just an account number. Can you explain exactly what
the
possibilities are, with examples?
Click to expand...
Click to expand...
 
One way would be to open and iterate through a recordset. The follwing
is air code that needs to be combined wit hthe code you already have:


...
Dim rsR As DAO.Recordset

'Set up regular expression object, as far as
...
oRE.Global = False

'Open recordset on table
Set rsR = CurrentDB.OpenRecordset("MyTable")

'Iterate through records in recordset
With rsR
Do Until rsR.EOF
'Execute search using current Description field
If Not IsNull(.Fields("Description").Value) Then
Set oMatches = oRE.Execute(.Fields("Description")Value)
If oMatches.Count > 0 Then
.Edit
.Fields("Transfercode").Value = oMatches(0).SubMatches(0)
.Fields("Acct Number").Value = Matches(0).SubMatches(1)
.Fields("Description").Value = oMatches(0).SubMatches(2)
.Update
End If
End If
Loop
End With

rsR.Close
Set rsR = Nothing
Set oRE = Nothing


Hi John,

Thanks for the adjustment. This is exactly want i try to do.

Now how can i update or insert the separated values into a table?

This is the last thing i will ask you.

Greetings,

Jason
John Nurick said:
Try this. The pattern now recognises

-optional transfercode at start of line (if the first character is a
letter, takes everything from there to the first space or tab)

-optional account number (5 to 12 digits and dots followed by a space or
tab)

-description (everything else).




Public Sub XXX()
Dim strDescription As String

Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection

strDescription = "TCode 123.45.92.1 That was the acct number"

Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?:([a-z][^ ]+)\s+)?(?:([0-9.]{5,12})\s+)?(.*)$"


' ^ anchors to start of string
' (?: ... ) groups terms in the expression
' ([a-z][^ ]+) matches and captures a substring
' starting with a letter and continuing
' with any characters except a space,
' i.e. transfercode
' \s+ matches white space, e.g. spaces or tabs
' ([0-9.]{5,12}) matches and captures 5 to 12 digits
' and dots, i.e. account number
' ? match preceding term 0 or 1 times
' (i.e. cover the case where there's
' no acct number or code)
' (.*)$ match the remainder of the line, i.e.
' the description.

oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.
Set oMatches = oRE.Execute(strDescription) ' Execute search.
If oMatches.Count > 0 Then
On Error Resume Next
Debug.Print "Transfercode: " & oMatches(0).SubMatches(0)
Debug.Print "Acct Number: " & oMatches(0).SubMatches(1)
Debug.Print "Description: " & oMatches(0).SubMatches(2)
Else
Debug.Print "Description: " & strDescription
End If

Set oMatches = Nothing
Set oRE = Nothing

End Sub


This is because you didn't describe the situation accurately in your
original message. You said:

The accountnr has at most 11 characters (numerical with dots) and the
transfercode has a variable length.

Now you're complaining because it won't accept a 12-character account
number
33.22.83.436
Change
{1,11}
in the pattern to
{1,12}
or the actual minimum and maximum numbers of characters in the account
number.


Similarly, when you said

The first word of the description field can be an accountnumber or
transfercode.

I assumed that you meant just that, and that all the remaining words in
the
description field are just the description. From what you now say it
appears
this is not the case, and that there can be both a transfer code and an
account number, or just an account number. Can you explain exactly what
the
possibilities are, with examples?




Hi John,

I've got your code working. However i do not understand some parts of
code. Could you explain me what or where this line of code looks at:

oMatches(0).SubMatches(0)

Because if i try the following string into the code, i get this:

?XXX("33.22.83.436 test")
Acct Number:
Transfercode:
Description: 33.22.83.436 test

Should be:

Acct Number: 33.22.83.436
Transfercode:
Description: test

And in case if there is some text before the acct number:

Acct Number: 33.22.83.436
Transfercode: ATM
Description: test
Click to expand...
Click to expand...
Click to expand...
 
Thanks John!

John said:
One way would be to open and iterate through a recordset. The follwing
is air code that needs to be combined wit hthe code you already have:


...
Dim rsR As DAO.Recordset

'Set up regular expression object, as far as
...
oRE.Global = False

'Open recordset on table
Set rsR = CurrentDB.OpenRecordset("MyTable")

'Iterate through records in recordset
With rsR
Do Until rsR.EOF
'Execute search using current Description field
If Not IsNull(.Fields("Description").Value) Then
Set oMatches = oRE.Execute(.Fields("Description")Value)
If oMatches.Count > 0 Then
.Edit
.Fields("Transfercode").Value = oMatches(0).SubMatches(0)
.Fields("Acct Number").Value = Matches(0).SubMatches(1)
.Fields("Description").Value = oMatches(0).SubMatches(2)
.Update
End If
End If
Loop
End With

rsR.Close
Set rsR = Nothing
Set oRE = Nothing


Hi John,

Thanks for the adjustment. This is exactly want i try to do.

Now how can i update or insert the separated values into a table?

This is the last thing i will ask you.

Greetings,

Jason
Try this. The pattern now recognises

-optional transfercode at start of line (if the first character is a
letter, takes everything from there to the first space or tab)

-optional account number (5 to 12 digits and dots followed by a space or
tab)

-description (everything else).




Public Sub XXX()
Dim strDescription As String

Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection

strDescription = "TCode 123.45.92.1 That was the acct number"

Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?:([a-z][^ ]+)\s+)?(?:([0-9.]{5,12})\s+)?(.*)$"


' ^ anchors to start of string
' (?: ... ) groups terms in the expression
' ([a-z][^ ]+) matches and captures a substring
' starting with a letter and continuing
' with any characters except a space,
' i.e. transfercode
' \s+ matches white space, e.g. spaces or tabs
' ([0-9.]{5,12}) matches and captures 5 to 12 digits
' and dots, i.e. account number
' ? match preceding term 0 or 1 times
' (i.e. cover the case where there's
' no acct number or code)
' (.*)$ match the remainder of the line, i.e.
' the description.

oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.
Set oMatches = oRE.Execute(strDescription) ' Execute search.
If oMatches.Count > 0 Then
On Error Resume Next
Debug.Print "Transfercode: " & oMatches(0).SubMatches(0)
Debug.Print "Acct Number: " & oMatches(0).SubMatches(1)
Debug.Print "Description: " & oMatches(0).SubMatches(2)
Else
Debug.Print "Description: " & strDescription
End If

Set oMatches = Nothing
Set oRE = Nothing

End Sub


On Fri, 8 Jul 2005 18:10:14 +0100, "John Nurick"


This is because you didn't describe the situation accurately in your
original message. You said:


The accountnr has at most 11 characters (numerical with dots) and the
transfercode has a variable length.

Now you're complaining because it won't accept a 12-character account
number
33.22.83.436
Change
{1,11}
in the pattern to
{1,12}
or the actual minimum and maximum numbers of characters in the account
number.


Similarly, when you said


The first word of the description field can be an accountnumber or
transfercode.

I assumed that you meant just that, and that all the remaining words in
the
description field are just the description. From what you now say it
appears
this is not the case, and that there can be both a transfer code and an
account number, or just an account number. Can you explain exactly what
the
possibilities are, with examples?





Hi John,

I've got your code working. However i do not understand some parts of
code. Could you explain me what or where this line of code looks at:

oMatches(0).SubMatches(0)

Because if i try the following string into the code, i get this:

?XXX("33.22.83.436 test")
Acct Number:
Transfercode:
Description: 33.22.83.436 test

Should be:

Acct Number: 33.22.83.436
Transfercode:
Description: test

And in case if there is some text before the acct number:

Acct Number: 33.22.83.436
Transfercode: ATM
Description: test
Click to expand...
Click to expand...
Click to expand...
 
Back
Top