How do I use pattern match in the instr function?

[Tom Connors]

Using Access2003. I have data sets with a 6 digit string embedded as such:
ACME INC 290452 447-DOE, JANE
WIDGETS CO & SONS 262378 897-SMITH, JOHN
etc etc
I want o extract everything up to the 6 digit portion (eg I want the "ACME
INC" portion). I thought I could use instr function with a pattern match of
###### to find the position of the 6 digits. I would then use the left
operator to trim up to that position.
Can I use pattern matching within instr ? This always returns 0:
test: InStr(1,[Vendor and Description],"######")

Any help would be appreciated.
Thanks
TC

 

[GeoBrooks]

TC,

I think brute force would be more successful. You'd have to build a
function to inspect each character in the string. Start with the leftmost
and discard it and test again if not numeric. In case there's a customer
name with a number in it, you should probably make sure that if the character
is numeric it is followed by five more and a space.

Use things like:
strIn = "WIDGETS CO & SONS 262378 897-SMITH, JOHN "
strTest = left(strIn,1)
IsNumeric(strTest)
if not, strIn = right(strIn,len(strIn)-1) and loop

Good luck.

George
[someone who wouldn't know code elegance if bitten by it!]

 

[John Nurick]

Hi Tom,

InStr() doesn't do pattern matching, only literal matches, and Like
doesn't tell you where it finds a match.

I've built a function that uses the VBScript regular expression object
for this sort of parsing; you'll find it at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

A pattern like this should do the job (everything up to a 6-digit
string:

(^.*)\s\d{6}

On Thu, 26 Jan 2006 11:21:05 -0800, "Tom Connors" <Tom

Using Access2003. I have data sets with a 6 digit string embedded as such:
ACME INC 290452 447-DOE, JANE
WIDGETS CO & SONS 262378 897-SMITH, JOHN
etc etc
I want o extract everything up to the 6 digit portion (eg I want the "ACME
INC" portion). I thought I could use instr function with a pattern match of
###### to find the position of the 6 digits. I would then use the left
operator to trim up to that position.
Can I use pattern matching within instr ? This always returns 0:
test: InStr(1,[Vendor and Description],"######")

Any help would be appreciated.
Thanks
TC

 

[Marshall Barton]

Using Access2003. I have data sets with a 6 digit string embedded as such:
ACME INC 290452 447-DOE, JANE
WIDGETS CO & SONS 262378 897-SMITH, JOHN
etc etc
I want o extract everything up to the 6 digit portion (eg I want the "ACME
INC" portion). I thought I could use instr function with a pattern match of
###### to find the position of the 6 digits. I would then use the left
operator to trim up to that position.
Can I use pattern matching within instr ? This always returns 0:
test: InStr(1,[Vendor and Description],"######")

No. Like is the only built-in operator or function that
uses wildcards.

You can easily write your own function that does what you
want though. Try something along the lines of this untested
air code:

Public Function GetCode(StrCode As String,
Pat As String
) As String
Dim k As Long

For k = 1 To Len(StrCode) - Len(Pat)
If Mid(StrCode, k) Like Pat Then
GetCode = Mid(StrCode, k, Len(Pat))
End If
Next k
End Function

 

[Marshall Barton]

Using Access2003. I have data sets with a 6 digit string embedded as such:
ACME INC 290452 447-DOE, JANE
WIDGETS CO & SONS 262378 897-SMITH, JOHN
etc etc
I want o extract everything up to the 6 digit portion (eg I want the "ACME
INC" portion). I thought I could use instr function with a pattern match of
###### to find the position of the 6 digits. I would then use the left
operator to trim up to that position.
Can I use pattern matching within instr ? This always returns 0:
test: InStr(1,[Vendor and Description],"######")

Well, that untested function certainly won't work :-(

Here's another (quick test) version that mimics InStr much
more closely:

Public Function InStrPat(Start As Variant, _
String1 As Variant, _
Optional String2 As Variant _
) As Variant
Dim lngStart As Long
Dim strText As String
Dim strPat As String
Dim Lg As Long, K As Long

InStrPat = Null
If IsMissing(String2) Then
If IsNull(Start) Or IsNull(String1) Then Exit Function
lngStart = 1
strText = Start
strPat = String1
Else
If IsNull(Start) Or IsNull(String1) Or IsNull(String2)
Then Exit Function
lngStart = Start
strText = String1
strPat = String2
End If

Lg = Len(strPat)
InStrPat = 0
For K = lngStart To Len(strText) - Lg
If Mid(strText, K, Lg) Like strPat Then
InStrPat = K
End If
Next K
End Function

 

[Tom Connors]

Thanks all !

Using Access2003. I have data sets with a 6 digit string embedded as such:
ACME INC 290452 447-DOE, JANE
WIDGETS CO & SONS 262378 897-SMITH, JOHN
etc etc
I want o extract everything up to the 6 digit portion (eg I want the "ACME
INC" portion). I thought I could use instr function with a pattern match of
###### to find the position of the 6 digits. I would then use the left
operator to trim up to that position.
Can I use pattern matching within instr ? This always returns 0:
test: InStr(1,[Vendor and Description],"######")

Well, that untested function certainly won't work :-(

Here's another (quick test) version that mimics InStr much
more closely:

Public Function InStrPat(Start As Variant, _
String1 As Variant, _
Optional String2 As Variant _
) As Variant
Dim lngStart As Long
Dim strText As String
Dim strPat As String
Dim Lg As Long, K As Long

InStrPat = Null
If IsMissing(String2) Then
If IsNull(Start) Or IsNull(String1) Then Exit Function
lngStart = 1
strText = Start
strPat = String1
Else
If IsNull(Start) Or IsNull(String1) Or IsNull(String2)
Then Exit Function
lngStart = Start
strText = String1
strPat = String2
End If

Lg = Len(strPat)
InStrPat = 0
For K = lngStart To Len(strText) - Lg
If Mid(strText, K, Lg) Like strPat Then
InStrPat = K
End If
Next K
End Function