separating street numbers and street names

B

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 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
 
B

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.

a a r o n _ k e m p f said:
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.
 
P

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

bird lover said:
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.

a a r o n _ k e m p f said:
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.
 
P

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.

raskew via AccessMonster.com said:
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

bird said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top