I notice that in this database, all addresses are followed by a comma and
each line ends with a 6 digit zip code.
How would I make a query that selects for "everything after the first
comma, excluding the last 6 characters"
In Access, a query can refer to VBA functions. In particular, you should
study these VBA functions in the help files or a good book, such as
"Access xxx Developer's Handbook" (where xxx is your version), published
by Sybex...
Right() -- returns the rightmost n characters from a string
Left() -- returns the leftmost n characters from a string
Mid() -- returns n characters from the middle of a string beginning with
character m
Instr() -- returns the position where string x occurs within string y.
InstrRev() -- same as Instr() but searches backward.
Len() -- Returns the length of a string in characters
These functions are Visual Basic functions, but you can use them within an
Access Query.
So if I have a column [Address] that contains "123 this street, some town
12345", and I know that all the records use a comma-space between the
street address and the town, I can use Instr() to find the position where
the comma-space is. The character *before that* is the end of the street
address, so...
Left([Address], InStr(1, [Address], ", ", 0) - 1)
Locates the ", " in the string and uses its numerical position to define
the end of the leftmost n characters I want to chop off to get the street
address by itself.
Further, if I know there is always a space between the city and zip code,
I can search the string for two things. (1) the comma-space helps me find
the beginning of the city and (2) the first space from the end of the
string helps me find the end of the city. The difference between these
two numerical positions helps me calculate how many characters to chop out
of the middle of the string to get the City by itself.
If I know that the zip code is always 5 digits, I can then simply chop the
rightmost 5 digits from the string to get the zip code by itself:
Right([Address], 5)
--
Peace & happy computing,
Mike Labosh, MCSD
Feed the children!
Save the whales!
Free the mallocs!
Thanks for the held -- works perfectly for address and zip fields, but
tends to produce some odd results with the city field.
Sorry to ask such simple questions; I am trying to figure out exactly how
you did what you did, but am somewhat inexperienced at this. Any
additional help would be appreciated.
Mike Labosh said:
The only thing consistent about the format of the field in question is
that the street address always ends in a comma, there is no state (all
addresses are in the same state), and the address, city and zip are
separated by a single space.
If I have a table Customers that looks like this...
CustomerID
CompanyName
Address
...
I will add two columns: City, Zip for the parsed out values:
CustomerID
CompanyName
Address
City
Zip
Then make a new query, switch to SQL View and paste this over what you
see:
UPDATE Customers SET Address = Left([Address],InStr(1,[Address],",
",0)-1), Customers.City = Mid([Address],InStr(1,[Address],",
",0)+2,InstrRev([Address]," ")-InStr(1,[Address],", ",0)-2),
Customers.Zip = Right([Address],5);
--
Peace & happy computing,
Mike Labosh, MCSD
Feed the children!
Save the whales!
Free the mallocs!
