sorting street names

[bird lover]

Address field contains following records
123 Main Street
23A Round Rock Road
567 W. 129th Street

I want to remove characters before first blank character so field
[streetname] has following data
Main Street
Round Rock Road
W. 129th Street

Using access 2003, and non-programmer. Suggested syntax to use in update
query would be appreciated.

 

[Wayne-I-M]

I assume you have a table called = TableName
and a field in the table called = Street

In a query create a calculated column with this

TextOnlyStreet:
Right([TableName]![Street],Len([TableName]![Street])-InStr([TableName]![Street]," "))

123 The Road will be The Road
456 Any Street will be Any Street

 

[Douglas J. Steele]

Alternatively,

TextOnlyStreet: Mid([TableName]![Street],InStr([TableName]![Street]," ") +
1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I assume you have a table called = TableName
and a field in the table called = Street

In a query create a calculated column with this

TextOnlyStreet:
Right([TableName]![Street],Len([TableName]![Street])-InStr([TableName]![Street],"
"))

123 The Road will be The Road
456 Any Street will be Any Street



--
Wayne
Manchester, England.

Address field contains following records
123 Main Street
23A Round Rock Road
567 W. 129th Street

I want to remove characters before first blank character so field
[streetname] has following data
Main Street
Round Rock Road
W. 129th Street

Using access 2003, and non-programmer. Suggested syntax to use in update
query would be appreciated.

 

[John W. Vinson/MVP]

Address field contains following records
123 Main Street
23A Round Rock Road
567 W. 129th Street

I want to remove characters before first blank character so field
[streetname] has following data
Main Street
Round Rock Road
W. 129th Street

Using access 2003, and non-programmer. Suggested syntax to use in update
query would be appreciated.

Either suggested solution will work, but unfortunately they'll both
stumble over addresses like:

1812 1/2 N. Maple
224 B Baker St.

and even your last example will sort all the "W" streets after all the
"E" streets - one might hope to get all the addresses on 129th street
together.

If you really need this level of control you may want to atomize the
address fields further, e.g. into AddressNo, Unit, Direction, Street
and Suffix (e.g. 123, <Null>, <Null>, Main, Street or 567, <Null>,
W, 129th, Street).

 

[Michael Gramelspacher]

Address field contains following records
123 Main Street
23A Round Rock Road
567 W. 129th Street

I want to remove characters before first blank character so field
[streetname] has following data
Main Street
Round Rock Road
W. 129th Street

Using access 2003, and non-programmer. Suggested syntax to use in update
query would be appreciated.

I put together a function that does as shown.

call splitaddress("123 1/2 N. Woodlawn Dr., Apt.5")
House number: 123
House number: 123 1/2
Direction: N.
Apartment: Apt.5
Street: Woodlawn Dr.

call splitaddress("123 C East Woodlawn Dr., Apt.5")
House number: 123
House number: 123C
Direction: East
Apartment: Apt.5
Street: Woodlawn Dr.

At this point it just displays the street address parts.
It is probably easy to trip up this function.

Sub SplitAddress(aStr As String)

Dim nStr As String
Dim nvar As Variant
Dim avar As Variant
Dim s As String
Dim i As Integer
Dim c As Integer
Dim e As Integer


' get numbers,spaces and dashes
nStr = FilterStr(aStr, "0123456789 -")

' convert double and triple staces to single spaces
nStr = Replace(Replace(Trim(nStr), " ", " "), " ", " ")

' split the number string
nvar = Split(nStr, " ", , vbTextCompare)

' split the original string and add space after commas
avar = Split(Replace(aStr, ",", ", "), " ", , vbTextCompare)

' get the house number
For i = LBound(nvar) To UBound(nvar)
Select Case i
Case 0 ' house number
If InStr(avar(i), nvar(i)) > 0 Then
Debug.Print "House number: " & avar(i)
c = 1
End If

' look for separated part of house number
If InStr(avar(i + 1), "/") > 0 Then
Debug.Print "House number: " & avar(i) & " " & avar(i + 1)
c = c + 1

ElseIf Len(Replace(avar(i + 1), ".", "")) = 1 Then
If Not (Replace(avar(i + 1), ".", "") = "N" _
Or Replace(avar(i + 1), ".", "") = "S" _
Or Replace(avar(i + 1), ".", "") = "E" _
Or Replace(avar(i + 1), ".", "") = "W") Then

' it belongs with house number
Debug.Print "House number: " & avar(i) & avar(i + 1)
c = c + 1

End If
End If

' check second position for direction

If Replace(avar(i + 1), ".", "") = "N" _
Or Replace(avar(i + 1), ".", "") = "S" _
Or Replace(avar(i + 1), ".", "") = "E" _
Or Replace(avar(i + 1), ".", "") = "W" _
Or avar(i + 1) = "North" _
Or avar(i + 1) = "South" _
Or avar(i + 1) = "East" _
Or avar(i + 1) = "West" Then

' this is direction
Debug.Print "Direction: " & avar(i + 1)
c = c + 1
End If

' check third position for direction

If Replace(avar(i + 2), ".", "") = "N" _
Or Replace(avar(i + 2), ".", "") = "S" _
Or Replace(avar(i + 2), ".", "") = "E" _
Or Replace(avar(i + 2), ".", "") = "W" _
Or avar(i + 2) = "North" _
Or avar(i + 2) = "South" _
Or avar(i + 2) = "East" _
Or avar(i + 2) = "West" Then

' this is direction
Debug.Print "Direction: " & avar(i + 2)
c = c + 1
End If
Case Else
End Select
Next i

' thee is no house number
If c = 0 Then
If Replace(avar(0), ".", "") = "N" _
Or Replace(avar(0), ".", "") = "S" _
Or Replace(avar(0), ".", "") = "E" _
Or Replace(avar(0), ".", "") = "W" _
Or avar(0) = "North" _
Or avar(0) = "South" _
Or avar(0) = "East" _
Or avar(0) = "West" Then

' this is direction
Debug.Print "Direction: " & avar(0)
c = c + 1
End If
End If

' get apartment number
If InStr(avar(UBound(avar)), nvar(UBound(nvar))) > 0 Then
Debug.Print "Apartment: " & avar(UBound(avar) - 1) _
& " " & avar(UBound(avar))
e = 2
End If

' get street name
For i = LBound(avar) + c To UBound(avar) - e
s = s & " " & avar(i)
Next i

Debug.Print "Street: " & Replace(Trim(s), ",", "")

End Sub


Function FilterStr(str1 As String, str2 As String) As String

Dim c As String
Dim s As String
Dim i As Integer

i = 1
Do Until i > Len(str1)

c = Mid(str1, i, 1)

If InStr(str2, c) Then
s = s & c
End If
i = i + 1
Loop

FilterStr = s
End Function