Extract housenumber from address

[Harmannus]

Hallo,

How can i extract the housenumber of a address or p.o.box?

E.g. P.O.Box 2564, Beverly Hils 2905a, Astreet 9-52 resulting in 2564, 2905a
or 9-52

Thanx for any tips!


Regards,

Harmannus

 

[Cheryl Fischer]

The only thing that I could suggest which would be close to what you want is
to use the Split() function to parse each address string using the space
between each part of the address as a delimiter. Then test each substring
returned with the Val() function. If this returns some value other than 0,
then you will - in most cases - have found the house number.

You can check out the Split() and Val() functions in VBA Help.

 

[Harmannus]

Hallo,

Thanx for the response. I checked the help file but i do not understand how
to combine these functions to get the results i pointed out.....


Regards,

Harmannus

 

[Cheryl Fischer]

You could use something like the following code. Arrays are not my
strongest skill, but here is how I would explain how the code works: It
will loop through a recordset based on a table (tblAddress) and read the
Addr field. The Split() function will parse each "word" in the Addr field,
splitting the words wherever it sees a space and storing each "word" in an
array named strAddrParts(). Each element in the array is numbered,
beginning with 0. As each element is read, it is evaluated with the Val()
function. If Val() identifies the "word" as a numeric, it will return a
value other than zero and write the entire contents of the "word" to the
housenumber field.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strAddrParts() As String
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddress", dbOpenDynaset)

rs.MoveFirst
Do While Not rs.EOF
ReDim strAddrParts(0)
strAddrParts = Split(rs!Addr)
' Read each element in the array
For i = 0 To UBound(strAddrParts)
If Val(strAddrParts(i)) <> 0 Then
' If the "word" evaluates to a number,
' write it to the housenumber field
rs.Edit
rs!housenumber = strAddrParts(i)
rs.Update
End If
Next i
rs.MoveNext
Loop

rs.Close
Set rs = Nothing



hth,

 

[Cheryl Fischer]

And, I should point out that because addresses are as non-standard as names,
this code may not work in all cases. For example:

One Penn Plaza - will not work, obviously

Apt 242, 4350 Jones Road - the code sample I gave you does not have a
routine to exit the For ... Next loop, so 4350 would be stored in the
HouseNumber field.

But, 4350 Jones Road, Apt 242 - would not work as you want it unless
you insert an Exit For in the For ... Next loop when the code evaluates its
first "word" as a number, as follows:

For i = 0 To UBound(strAddrParts)
If Val(strAddrParts(i)) <> 0 Then
' If the "word" evaluates to a number,
' write it to the housenumber field
rs.Edit
rs!housenumber = strAddrParts(i)
rs.Update
Exit For
End If
Next i

 

[Ken Snell]

Just one thing to watch out for:

Val("125th") = 125

So in the address of 1345 125th Street, two of the substrings would return
numbers other than zero.

One might be able to use a test of IsNumeric first, and then take the
value.... oops, but then the street number of 123A Orchardview Drive would
not have a street address because it would fail the IsNumeric test.

Could be a "sticky wicket" problem if the data are not clean for just
numbers in the street addresses!

 

[Ken Snell]

Cheryl -

Often it's easier to handle arrays by dimm'g as Variant, and then the Split
action can turn that variable into an array if the data warrant it:

Dim strAddrParts As Variant

Then no ReDim would be needed in the code and the rest of your example
should work just fine.

 

[Cheryl Fischer]

Thanks!