UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parsing An Address, Access 2016    
 
   
waitingroomz
post Oct 13 2019, 11:40 AM
Post#1



Posts: 93
Joined: 29-October 18



Good afternoon UA!

I found the below code that works to parse an address, but am trying to figure out how to add a variable that pulls the number after the street in the following example addresses:

6852 NW 16 ST 295
7005 NW 16 ST 170
1760 N ROCKWELL AVE 200
1320 N ROCKWELL AVE C
1111 N ST CHARLES AVE 16

Right now it breaks it up and leaves the Street Name like so:

16 ST 295
16 ST 170
ROCKWELL AVE 200
ROCKWELL AVE C
ST CHARLES AVE 16

Working correctly it would create another ParseAddress as Unit Number, leaving the Street Name separate, and show this:

295
170
200
C
16

CODE
Public Function ParseAddress(strInput As String, intPos As Integer) As String
'   Sample syntax:
'   ? ParseAddress("221b Baker Street",1)
'   221b
'   ? ParseAddress("221b Baker Street",3)
'   Baker
'   intPos - Returns:
'      1   - Street Number
'      2   - Compass Heading (if any)
'      3   - Street Name
'      4   - Street Type (CT, DR, WAY, etc.)
    Dim strStreetNumber As String, strCompass As String
    Dim strStreetName As String, strStreetType As String
    
    If Len(strInput & "") > 0 Then
'       Street Number (assume it's the bit before the first space)
        strStreetNumber = Left(strInput, InStr(1, strInput, " ") - 1)
'       Strip away the street number
        strInput = Trim(Mid(strInput, InStr(1, strInput, " ") + 1))
        
'       Compass Heading (if any)
        Select Case UCase(Left(strInput, InStr(1, strInput, " ") - 1))
'      If one of these appears after the street number...
        Case "N", "S", "E", "W", "NORTH", "SOUTH", "EAST", "WEST"
'           Assign the text to the variable...
            strCompass = Left(strInput, InStr(1, strInput, " ") - 1)
'           ...and strip it out of the input text
            strInput = Trim(Mid(strInput, InStr(1, strInput, " ") + 1))
        End Select
    
'       Street Type
        Select Case UCase(Mid(strInput, InStrRev(strInput, " ") + 1))
        
'       If the last word of the input is one of the following:
        Case "AVE", "AVENUE", "BLVD", "BOULEVARD", "CT", "COURT", _
             "DR", "DRIVE", "PASS", "PL", "PLACE", "RD", "ROAD", _
             "ST", "STREET", "WAY"
            
'           Assign the last word to the variable...
            strStreetType = Mid(strInput, InStrRev(strInput, " ") + 1)
'           ...and strip it out of the input text
            strInput = Left(strInput, InStrRev(strInput, " ") - 1)
        End Select
'       Street Name (everything else has been trimmed)
        strStreetName = strInput
    End If
'   Return the segment requested
    Select Case intPos
    Case 1
        ParseAddress = strStreetNumber
    Case 2
        ParseAddress = strCompass
    Case 3
        ParseAddress = strStreetName
    Case 4
        ParseAddress = strStreetType
    End Select
    
End Function


Any help is much appreciated!
Go to the top of the page
 
arnelgp
post Oct 13 2019, 11:51 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


introduce another function:
CODE
Public Function split_text(ByVal s As String, ByVal i As Integer, Optional ByVal delim As String = ",") As String
    ' s     = the string to split
    ' i     = the index number (1 base)
    ' delim = the delimiter string
    '
    On Error GoTo err_handler
    split_text = Split(s, delim)(i - 1)
    Exit Function
err_handler:
    split_text = ""
End Function



?split_text(ParseAddress("ROCKWELL AVE C",3), 2, " ")

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Jeff B.
post Oct 13 2019, 12:54 PM
Post#3


UtterAccess VIP
Posts: 10,326
Joined: 30-April 10
From: Pacific NorthWet


?Will every address have a UnitNumber? (I'm guessing not.)

You may be on the border of needing to USB (use someone's brain). Humans seem to be able to parse ambiguity much better than code.

Good luck!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
zocker
post Oct 13 2019, 12:57 PM
Post#4


Utterly Eccentric and Moderator
Posts: 4,081
Joined: 4-March 00
From: Bristol / Ipswich / Spain


This can be simplified using inbuilt string functions:

CODE
Dim schString As String
Dim lenString As Integer
Dim posLastSpace As Integer
Dim lastString As String

schString = "1760 N ROCKWELL AVE 200 Zpo"

   'get schString length
    
   lenString = Len(schString)
    
    'get pos of last space

   posLastSpace = InStrRev(schString, " ")

    'last chars are Length of string minus pos. of last space
    
   lastString = Right(schString, lenString - posLastSpace)


MsgBox lastString


or simplified further:

CODE
        
    Dim schString As String
    Dim lastString As String
    
    schString = "1760 N ROCKWELL AVE 200 Zpo"
    
    lastString = Right(schString, Len(schString) - InStrRev(schString, " "))



All the best!

Zocker

--------------------
I would like to remind members and visitors that UtterAccess is a NON SMOKING website. In that respect, it is the worlds first Thank you for not smoking.
Go to the top of the page
 
orange999
post Oct 13 2019, 05:49 PM
Post#5



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Finding code that matches your requirement is amazing --especially if the format of the addresses involved is not consistent.
I agree with Jeff B. --this usually requires what we used to call IEH - Eyeballs (an old IBM joke) -->human intervention.
Rarely are addresses consistent in format unless pre-processed with some commercial($$$) software.

Update: I checked in some databases I have. It seems that function came from Doctor9 back in Dec 2015 works well if your data meets the format requirement.
This post has been edited by orange999: Oct 13 2019, 06:09 PM

--------------------
Good luck with your project!
Go to the top of the page
 
waitingroomz
post Oct 19 2019, 01:53 PM
Post#6



Posts: 93
Joined: 29-October 18



Indeed it was his that had the original code.

So, I am still having trouble with this and I feel that it can be easily solved by just pulling all characters to the right of:

"AVE", "AVENUE", "BLVD", "BOULEVARD", "CIR", "CT", "COURT", _
"DR", "DRIVE", "LN", "PASS", "PL", "PLACE", "RD", "ROAD", _
"ST", "STREET", "WAY"

And then separate it again after the word.
Go to the top of the page
 
moke123
post Oct 20 2019, 03:03 AM
Post#7



Posts: 1,368
Joined: 26-December 12
From: Berkshire Mtns.


I would think that list would need to be exhaustive to cover all possible street types.
Expressway, Turnpike, Path, Alley, Terrace, Walk, Way, Plaza, Extension, Oval, Crossing, Dell, etc. Not including any abbreviations or variations.
At the very least I would think you'd need a table to allow for additions if encountered.

What happens with names like North Street or Court Street Extension?
Go to the top of the page
 
dmhzx
post Oct 20 2019, 04:26 AM
Post#8



Posts: 7,115
Joined: 22-December 10
From: England


As an add on to Moke123

Yes if you can rely on the overall structure, set up a table with all the currently possible last words,
Then in your code output something like "XXX" if none on the last words are there, form where it's easy to list all the XXX, and look at them, and decide whether to add a record to the table, or change the address.

Make sure you remove the XXX before a re-run, or if there is an XXX look at the penultimate word.

I've used a technique like this to standardise nomenclature on import.

Simple table
What the import says (PK) , What I want to use


Then it's straightforward to always store , for example "Street"

Make sure that the table includes an entry for Street.

Examplle

St , Street
Stree, Street
Street, Street


And that your code tell you when it finds a new one.


Go to the top of the page
 
projecttoday
post Oct 20 2019, 06:49 AM
Post#9


UtterAccess VIP
Posts: 11,205
Joined: 10-February 04
From: South Charleston, WV


Make sure the street, avenue, etc. is a separate field going forward. Call it "road type" or "road designation" or something.

In Court Street, the name of the street is Court. The road type is street. In Court Street Extension, the name of the street is Court Street, the road type is extension.

Don't forget Trail.

And, of course, how could you possibly forget Access?

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Oct 20 2019, 07:34 AM
Post#10


UtterAccess VIP
Posts: 1,818
Joined: 4-June 18
From: Somerset, UK


And just to point out the blindingly obvious...
Its much better to save each part of the address as separate fields then concatenate them as required to create an address block or address line.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
arnelgp
post Oct 20 2019, 07:40 AM
Post#11



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


is that Normal?
you'll concatenate them when presenting in form for data entry or some sort.
then when edited, break them again, hmm.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
projecttoday
post Oct 20 2019, 07:48 AM
Post#12


UtterAccess VIP
Posts: 11,205
Joined: 10-February 04
From: South Charleston, WV


No. Separate them in the table. That means separating them when they are entered for the first time. Separate them for changing, also. Concatenate them for display-only purposes.

--------------------
Robert Crouser
Go to the top of the page
 
orange999
post Oct 20 2019, 08:52 AM
Post#13



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


waitingroomz,

You may find this addressing guide helpful even if only as a reference. I'm sure there are similar guides for specific countries/postal systems.
This post has been edited by orange999: Oct 20 2019, 08:53 AM

--------------------
Good luck with your project!
Go to the top of the page
 
isladogs
post Oct 20 2019, 11:08 AM
Post#14


UtterAccess VIP
Posts: 1,818
Joined: 4-June 18
From: Somerset, UK


My point was indeed that each part of the address should be saved as separate fields in the table.
Concatenation would be used when needed to create an address block or address line for display in forms or reports e.g. Mailing labels
Similarly for use in mail merge letters.

Frankly there are so many exceptions to address styles that it is impossible for any procedure to successfully parse all addresses into their component parts without human intervention
This article explains why Falsehoods programmers believe about addresses

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
orange999
post Oct 20 2019, 11:33 AM
Post#15



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Colin,

The falsehoods are "Very true". There are many who have spent a lifetime parsing/cleansing names and addresses.

--------------------
Good luck with your project!
Go to the top of the page
 
dmhzx
post Oct 20 2019, 12:59 PM
Post#16



Posts: 7,115
Joined: 22-December 10
From: England


This does mean that you need to be very disciplined about storing the data in the first place.
If 'number' is a field, you must ensure that only numbers go in
If "street" then only streets/roads etc go in.
This can lead to even more difficulties if people can enter thing like "The Gables" in a number field, and where "The Gables doesn't have a number.
Where I live, I don't actually know my street name, and my home has a name, not a number.

Addresses can be a pain.
The last time I had an address clean to do, I split it between system and Human, where the parson decided which of all the possible similar addresses were the 'same', and what the correct version was (Possibly none of them).
I ran this in a friendly competition with someone who though they could do it all in code.
My person had finished the entire job before my competitor was anywhere near ready to run.
Go to the top of the page
 
GroverParkGeorge
post Oct 20 2019, 01:13 PM
Post#17


UA Admin
Posts: 36,063
Joined: 20-June 02
From: Newcastle, WA


I know a person who made a good living cleaning up mailing and email lists for customers. He had developed some very sophisticated algorithms to find duplicates and near duplicates. He also sent his files off to a service that validated them against the US Postal service database (of course he's here in the US where that's the right agency to do that sort of thing) and to other services that validated email addresses somehow. In the final step, he had a residual list of "suspect" addresses and email addresses and examined them, by eye, to make the final calls.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 09:22 PM