separating street numbers and street names

[bird lover]

Field [address] contains multiple addresses, i.e 121 Main Street, 106-20A
20th Avenue.
Goal: move data into two fields, [streetsort] and [numbersort] so that
[streetsort] contains just addresses, i.e Main Street, Broadway, 5th Avenue,
while [streetnumber] contains just numbers. Purpose: to sort street names,
then subsort via street numbers.
I use access 2003, and don't know programming. If you can provide sample
syntax for an update or other type query, same would be appreciated.

 

[a a r o n _ k e m p f]

I'd get a real 'address verification' solution.. for example I've been
evaluating these for my employer.

I'd gladly reccomend one of these two:

a) QAS / Experian
b) Satori Software

 

[bird lover]

My bad. My request was unclear. There are a number of records, each with an
address, [address]i.e. 125 Main Street, 14 East 14th Street. I want to
update into field [streetname] just the streetname, i.le. Main Street, Howard
Ave., so I can sort on streetname. I'd like to stick to access. Sorry,
Aaron for not being adequately specific.

I'd get a real 'address verification' solution.. for example I've been
evaluating these for my employer.

I'd gladly reccomend one of these two:

a) QAS / Experian
b) Satori Software

Field [address] contains multiple addresses, i.e 121 Main Street, 106-20A
20th Avenue.
Goal: move data into two fields, [streetsort] and [numbersort] so that
[streetsort] contains just addresses, i.e Main Street, Broadway, 5th Avenue,
while [streetnumber] contains just numbers. Purpose: to sort street names,
then subsort via street numbers.
I use access 2003, and don't know programming. If you can provide sample
syntax for an update or other type query, same would be appreciated.

 

[Pete D.]

As I cannot really see what your data looks like it is hard to tell you how
to do this but this might give you some idea.

http://www.tek-tips.com/viewthread.cfm?qid=1038663

My bad. My request was unclear. There are a number of records, each with
an
address, [address]i.e. 125 Main Street, 14 East 14th Street. I want to
update into field [streetname] just the streetname, i.le. Main Street,
Howard
Ave., so I can sort on streetname. I'd like to stick to access. Sorry,
Aaron for not being adequately specific.

I'd get a real 'address verification' solution.. for example I've been
evaluating these for my employer.

I'd gladly reccomend one of these two:

a) QAS / Experian
b) Satori Software

Field [address] contains multiple addresses, i.e 121 Main Street,
106-20A
20th Avenue.
Goal: move data into two fields, [streetsort] and [numbersort] so that
[streetsort] contains just addresses, i.e Main Street, Broadway, 5th
Avenue,
while [streetnumber] contains just numbers. Purpose: to sort street
names,
then subsort via street numbers.
I use access 2003, and don't know programming. If you can provide
sample
syntax for an update or other type query, same would be appreciated.

 

[Pete D.]

I agree, without a lot of logic checks it will be tough to get total
solution but you may be able to query together 80% solution after parsing it
into pieces. I have used zip code table to reverse lookup city and state
but this still leave problems with two row address, and throw in a building
number.

Given the vast inconsistencies in addresses, that this is going to be
potentially inaccurate.

For most addresses, this little UDF might suffice:
*****************************************************************
Function ySplit(ByVal pTarget As String, _
pItem As String, _
Optional ShowLeft As Boolean = True) _
As String

'*******************************************
'Purpose: Splits a string to the left or
' right of pItem
'Coded by: raskew
'Inputs: 1) ySplit("The quick+ brown fox", "+", True)
' 2) ySplit("The quick+ brown fox", "+", False)
'Output: 1) The quick
' 2) brown fox
'*******************************************

Dim strLeft As String
Dim strRight As String
Dim n As Integer

n = InStr(pTarget, pItem)
If n = 0 Then
ySplit = ""
Else
ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
strLeft = Left(pTarget, n - 1)
strRight = Mid(pTarget, n + 1)
ySplit = Trim(IIf(ShowLeft, strLeft, strRight))
End If

End Function
*****************************************************************
Examples from the immediate (debug) window:

x = "106-20A 20th Avenue"
? ysplit(x, " ", True)
106-20A
? ysplit(x, " ", False)
20th Avenue

Where this would fail, however, is with an address like:
106-20A Apt. 2C 20th Avenue

You may want to look at the split() function.

Bob

My bad. My request was unclear. There are a number of records, each with
an
address, [address]i.e. 125 Main Street, 14 East 14th Street. I want to
update into field [streetname] just the streetname, i.le. Main Street,
Howard
Ave., so I can sort on streetname. I'd like to stick to access. Sorry,
Aaron for not being adequately specific.

I'd get a real 'address verification' solution.. for example I've been
evaluating these for my employer.

[quoted text clipped - 14 lines]

I use access 2003, and don't know programming. If you can provide
sample
syntax for an update or other type query, same would be appreciated.