String handling question

[Tcs]

I have field names with data I'm trying to pull apart. Ex:

1234 North Horse Hockey Ln 567
North Horse Hockey Ln 678
1235 Horse Hockey Ln 987
4234 North Horse Hockey Ln
Horse Hockey Ln
3241 North St

(My records are not fixed width, I just showed them this way for ease of
reading.)

I'm trying to grab the "North" (direction) type streets withOUT grabbing "North
St". I'm trying to use the following, but having no luck. Access (2k) says it
can't find any, and I *KNOW* they exist.

InStr([SitusAddress]," North St",Null) And InStr([SitusAddress]," North ")
InStr([SitusAddress]," North St",0) And InStr([SitusAddress]," North ")

Anybody see what I'm doing wrong?

Thanks much, in advance,

Tom

 

[John Nurick]

Hi Tom,

This sort of thing is usually best handled with a regular expression.
Here's a procedure that seems to do the job on your sample data:


Public Function ParseAddress35(V As Variant, Item As Long) As Variant

Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim Regex As String
Dim Result As String

ParseAddress35 = Null 'default value

If IsNull(V) Then
Exit Function
End If

Set oRE = CreateObject("Vbscript.Regexp")

'Assemble regular expression to parse address
'Item 0: Optional street number
Regex = "^\s*(\d+\s+)?"
'Item 1: Optional North/South etc.
Regex = Regex & "(North|South|East|West)?"
'Item 2: Street Name & Type, Item 3: Street Type
'(Add more types to list if required)
Regex = Regex & "\s*(\b\w+\b.*?(St|Ln|Bvd|Rd|Dve|Ave|Way|Cr|Tce)?)"
'Item 4: Optional apartment number
Regex = Regex & "\s*?(\s+\d+)?\s*$"

With oRE
.Pattern = Regex
.IgnoreCase = True
Set oMatches = .Execute(CStr(V))
End With

If oMatches.Count > 0 Then
With oMatches(0)
Result = Trim(.SubMatches(Item))
If Item = 2 Then 'Trim street type off street name
Result = Trim(Left(Result, Len(Result) - Len(.SubMatches(3))))
End If
ParseAddress35 = Result
End With
End If
End Function

I have field names with data I'm trying to pull apart. Ex:

1234 North Horse Hockey Ln 567
North Horse Hockey Ln 678
1235 Horse Hockey Ln 987
4234 North Horse Hockey Ln
Horse Hockey Ln
3241 North St

(My records are not fixed width, I just showed them this way for ease of
reading.)

I'm trying to grab the "North" (direction) type streets withOUT grabbing "North
St". I'm trying to use the following, but having no luck. Access (2k) says it
can't find any, and I *KNOW* they exist.

InStr([SitusAddress]," North St",Null) And InStr([SitusAddress]," North ")
InStr([SitusAddress]," North St",0) And InStr([SitusAddress]," North ")

Anybody see what I'm doing wrong?

Thanks much, in advance,

Tom

 

[John Nurick]

Small improvement:


Public Function ParseAddress36(V As Variant, Item As Long) As Variant

Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim Regex As String
Dim Result As String

ParseAddress36 = Null 'default value

If IsNull(V) Then
Exit Function
End If

Set oRE = CreateObject("Vbscript.Regexp")

'Assemble regular expression to parse address
'Item 0: Optional street number
Regex = "^\s*(\d+\s+)?"
'Item 1: Optional North/South etc.
Regex = Regex & "(North|South|East|West)?"
'Item 2: Street Name (one or more words, required)
Regex = Regex & "\s*(?\b\w+\b.*?)"
'Item 3: Optional Street Type (add more types to list if needed)
Regex = Regex & "(?:\s+(St|Ln|Bvd|Rd|Dve|Ave|Way|Cr|Tce))?)"
'Item 4: Optional apartment number
Regex = Regex & "\s*?(\s+\d+)?\s*$"

With oRE
.Pattern = Regex
.IgnoreCase = True
Set oMatches = .Execute(CStr(V))
End With

If oMatches.Count > 0 Then
ParseAddress36 = Trim(oMatches(0).SubMatches(Item))
End If
End Function

Hi Tom,

This sort of thing is usually best handled with a regular expression.
Here's a procedure that seems to do the job on your sample data:


Public Function ParseAddress35(V As Variant, Item As Long) As Variant

Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim Regex As String
Dim Result As String

ParseAddress35 = Null 'default value

If IsNull(V) Then
Exit Function
End If

Set oRE = CreateObject("Vbscript.Regexp")

'Assemble regular expression to parse address
'Item 0: Optional street number
Regex = "^\s*(\d+\s+)?"
'Item 1: Optional North/South etc.
Regex = Regex & "(North|South|East|West)?"
'Item 2: Street Name & Type, Item 3: Street Type
'(Add more types to list if required)
Regex = Regex & "\s*(\b\w+\b.*?(St|Ln|Bvd|Rd|Dve|Ave|Way|Cr|Tce)?)"
'Item 4: Optional apartment number
Regex = Regex & "\s*?(\s+\d+)?\s*$"

With oRE
.Pattern = Regex
.IgnoreCase = True
Set oMatches = .Execute(CStr(V))
End With

If oMatches.Count > 0 Then
With oMatches(0)
Result = Trim(.SubMatches(Item))
If Item = 2 Then 'Trim street type off street name
Result = Trim(Left(Result, Len(Result) - Len(.SubMatches(3))))
End If
ParseAddress35 = Result
End With
End If
End Function

I have field names with data I'm trying to pull apart. Ex:

1234 North Horse Hockey Ln 567
North Horse Hockey Ln 678
1235 Horse Hockey Ln 987
4234 North Horse Hockey Ln
Horse Hockey Ln
3241 North St

(My records are not fixed width, I just showed them this way for ease of
reading.)

I'm trying to grab the "North" (direction) type streets withOUT grabbing "North
St". I'm trying to use the following, but having no luck. Access (2k) says it
can't find any, and I *KNOW* they exist.

InStr([SitusAddress]," North St",Null) And InStr([SitusAddress]," North ")
InStr([SitusAddress]," North St",0) And InStr([SitusAddress]," North ")

Anybody see what I'm doing wrong?

Thanks much, in advance,

Tom