Table Join based on like string match.

[PeteG]

Access 2003, OS is XP.

Can/how would I create a join between two tables returning only those records from table two that has a common 10 digit string found within the field?

Table one contains purchase order numbers field (text) (ex. 6200005330, 4201005875 etc) from our system of record with a char len of 10.
Table two contains miscellaneous shipment reference number information (text) sent electronically by our service providers. This information will contain the purchase order numbers however it is frequently hidden between alpha/numeric characters (ex. 6200005330/10/1 or PO4201005875 etc…).

I would like join and return the data from table two when I can find a string match with the data in table 1 (ex 6200005330 = 6200005330/10/1 or 4201005875 = PO4201005875).

 

[Dankniseley]

*********************************************************

' stolen from Richie(UK)
' in post http://www.mrexcel.com/board2/viewtopic.php?t=103884&highlight=regexp
Function RE10(strData As String) As String
Dim RE As Object, REMatches As Object

Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
End With

Set REMatches = RE.Execute(strData)
RE10 = REMatches(0)

End Function
**********************************************************

quick question I am trying to use this function but when I copy and paste it into a new module and design a quey that uses this function I get and error but the query seems to run one line at a time I get the following error.

"Invalid procedure Call or argument"

any help will be great

Thx

 

[Dankniseley]

code:
--------------------------------------------------------------------------------
' stolen from Richie(UK)
' in post http://www.mrexcel.com/board2/viewtopic.php?t=103884&highlight=regexp
Function RE10(strData As String) As String
Dim RE As Object, REMatches As Object

Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
End With

Set REMatches = RE.Execute(strData)
RE10 = REMatches(0)

End Function

********************************************

I have run this code in Excel as a macro and it works great but when i try to load it into a module and build a query using this function I get an error.

procedure call or arguement error. Can some one help me turn this into a module to use as a user built function to inbed into a query.

thanks Dan

 

[bat17]

I have just had a play with this and it seems as through the error is there if there is no 10 digit number. by adding error trapping we can force it to return "0000000000" which you could filter on to check why it did not work.

' stolen from Richie(UK)
' in post http://www.mrexcel.com/board2/viewtopic.php?t=103884&highlight=regexp
Function RE10(strData As String) As String
    Dim RE As Object, REMatches As Object
   On Error GoTo NoNumber
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
    End With
    
    Set REMatches = RE.Execute(strData)
    RE10 = REMatches(0)
Exit Function
NoNumber:
RE10 = "0000000000"
End Function

HTH

Peter

 

[PeteG]

Wow…thanks Bat, Andrew. Took me a few days to figure out how to get it to run in Access and to understand what it is doing. This is great!!! Does exactly what I needed.

Thank you very much for you help. ray:

Regards.
Pete