Re: Separating Addresses
From: Chris2 (indanthrene.NOTVALID_at_GETRIDOF.yahoo.com)
Date: 03/17/04
- Next message: James: "Totals Query?"
- Previous message: John Vinson: "Re: Querying Dates"
- In reply to: Mike DeNuccio: "Separating Addresses"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Mar 2004 07:40:45 GMT
"Mike DeNuccio" <mike@denuccio.net> wrote in message
news:%23199ce%23CEHA.1544@TK2MSFTNGP09.phx.gbl...
I have a database of addresses that are inputed like this:
100 East Sunshine Street
200 East Sunshine Street, #400
3456 W Jones
1 Jones, Apt 2A
I need to divide this Address field into thee parts: House Number; Street;
Apt (only if it has an apartment which is indicated by the comma in the
field)
I know how to separate the House Number from the Address field:
Left([address],InStr([address]," "))
I know how to separate the Street from the Address if there is no apartment:
Right([address],(Len([address])-(InStr(1,[address]," "))))
I know how to separate the Apartment from the Address:
Trim(Right([address],(Len([address])-(InStr(1,[address],", ")))))
So here's my problem - if the Address has a apartment, I cannot get only the
Street to appear. Basically, I need everything in between the first space
and the comma to appear in a new field called Street. For example, in the
second example above, it would divide into this:
House Number: 200
Street: East Sunshine Street
Apt: #400
Seems easy, but I simply cannot figure out why it won't work.
The formula I was using for addresses with Apts is:
Trim(Mid([address],InStr([address]," ",InStr([address],", ")-1))) but this
does not return anything. I did this formula because in English what I was
trying to do was to say that the start character is the first space in the
field. The end character is the one with the comma (in the format
Mid([String], Start, End). Then I realized the formula is Mid(String,
Start, Length).
So I tried this: Trim(Mid([address],InStr([address],"
",Len([address])-InStr([address],", ")-1 ) ) )
Any help would be appreciated! If I am doing a very long version for the
ones I have, please correct those as well.
Thanks in advance. If possible, reply to me AND post.
#####################
Mike,
I struggled for ten or twenty minutes with all the various arguments and
(), with no luck; this late at night, it all looks the same.
Here it is encapsulated in VBA:
Public Function CaptureStreet(FullStreetAddress As String) As String
Dim StreetName As String
Dim FirstSpacePosition As Byte
Dim LengthToLastSpace As Byte
Dim ReversedFullStreetAddress As String
Dim ReversedFirstSpacePosition As Byte
Dim FullAddressLength As Byte
FirstSpacePosition = InStr(FullStreetAddress, " ")
ReversedFullStreetAddress = StrReverse(FullStreetAddress)
ReversedFirstSpacePosition = InStr(ReversedFullStreetAddress, " ")
FullAddressLength = Len(FullStreetAddress)
LengthToLastSpace = FullAddressLength - _
FirstSpacePosition - _
ReversedFirstSpacePosition
StreetName = Trim(Mid(FullStreetAddress, _
FirstSpacePosition, _
LengthToLastSpace))
CaptureStreet = StreetName
End Function
It worked for both the addresses shown above in the sample data, plus some
other basic combinations, like altering the length of the Street Number and
the Apartment Number, but that only amounted to five minutes testing, so it
may bear closer scrutiny.
Sincerely,
Chris O.
- Next message: James: "Totals Query?"
- Previous message: John Vinson: "Re: Querying Dates"
- In reply to: Mike DeNuccio: "Separating Addresses"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
