I have a database of addresses that need to be parsed

  • Thread starter Thread starter Shawn Johnson
  • Start date Start date
S

Shawn Johnson

I need to Parse a combined mailing address:

15 Main St Apt 10

into a parsed output that will put the "15" in a civic number field, the
"Main" into a Street Name field the "St" into a street type field, the "Apt"
into a Unit Type field and lastly the "10" into a Unit Number field.

Could someone please give me some direction with this problem.

Shawn J
 
Hi, Shawn.

My approach to this problem would be to add the required fields to my
existing table, and then create two queries. The first query would select
the table's primary key, and create a series of calculated fields, each of
which is a substring of the address,

Combined Street Address 15
FirstSubstring Main St Apt 10
SecondSubstring St Apt 10
ThirdSubstring Apt 10
FourthSubstring 10

using the Instr() function to find the location of the space, and the Mid()
function to return the substring starting with the next character. So the
formula for FirstSS is, as entered in the design grid:

FirstSS: Mid([StreetAddress],InStr([StreetAddress]," ")+1)

Subsequent substrings use the same functions, but operate on the previous
substring, e.g., the formula for the second substring is:

SecondSS: Mid([FirstSS],InStr([FirstSS]," ")+1)

The second query would update the new fields, using the Left and Instr
functions. Show the original table and the first query, linked on the
primary key. Drag the fields to be updated to the query grid, and select
Query, Update from the design menu.

In the Update To: row, use the Left and Instr functions, operating on the
appropriate substring, e.g., to update the StreetName to "Main":

Left([qryStreetParse1].[FirstSS],InStr([qryStreetParse1].[FirstSS]," ")-1)

The final field, UnitNumber, simply uses the fourth substring:

StreetParse.UnitNumber = [qryStreetParse1].[FourthSS]

The entire SQL for the 2 queries, based on a table named StreetParse, and
queries named qryStreetParse1 and qryStreetParse2 are:

qryStreetParse1
===========

SELECT StreetParse.ID, StreetParse.StreetAddress,
Mid([StreetAddress],InStr([StreetAddress]," ")+1) AS FirstSS,
Mid([FirstSS],InStr([FirstSS]," ")+1) AS SecondSS,
Mid([SecondSS],InStr([SecondSS]," ")+1) AS ThirdSS,
Mid([ThirdSS],InStr([ThirdSS]," ")+1) AS FourthSS
FROM StreetParse;


qryStreetParse2
===========

UPDATE StreetParse INNER JOIN qryStreetParse1 ON StreetParse.ID =
qryStreetParse1.ID SET StreetParse.StreetNumber =
Left([qryStreetParse1].[StreetAddress],InStr([qryStreetParse1].[StreetAddress],"
")-1), StreetParse.StreetName =
Left([qryStreetParse1].[FirstSS],InStr([qryStreetParse1].[FirstSS]," ")-1),
StreetParse.StreetType =
Left([qryStreetParse1].[SecondSS],InStr([qryStreetParse1].[SecondSS]," ")-1),
StreetParse.UnitType =
Left([qryStreetParse1].[ThirdSS],InStr([qryStreetParse1].[ThirdSS]," ")-1),
StreetParse.UnitNumber = [qryStreetParse1].[FourthSS];

Once you've verified the parsing, you can delete the combined field if you
wish.

Also, be aware that I've assumed that there is only one space between each
field, which may or may not be the case. Use the LTrim() function to strip
off any leading spaces from your substrings, e.g., for the first substring:

FirstSS: LTrim(Mid([StreetAddress],InStr([StreetAddress]," ")+1))

Hope that helps. Good luck.
Sprinks
 
Back
Top