[AccessD] Numbers within a street

Arthur

Just my 2-cents ...

I would not assume that 2333-1070 Queen Street has 2333 for the numeric ...
Many places using the hyphenated format actually are putting the
unit/suite/lot number in front of the address ... Your example might really
be 1070 Queen Street, Unit 2333 ... This is quite prevalent in Canada, for
example

I know that only makes it murkier :-)

Best regards,
Bob Gajewski

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, September 14, 2011 16:31 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Numbers within a street

I am working on a political campaign management app. The thing I need to do
is post reports that identify the even and odd numbers on a given street. I
inherited the db and have freedom to change it (it was written by amateurs
and they know it and there are no hard feelings if I make a change, insofar
as said change increases productivity).

So... given a current field called StreetAddress, I want to break it into
two fields, StreetNumber and StreetAddress, so that I can filter the even
numbers for one report  and the odd numbers for an identical report. How can
I intelligently extract the data and populate my new columns?

Example data:

123 Normal Street ' easy
234A Abnormal Street ' a tad trickier
2333-1070 Queen Street  ' the 2333 part should fall into the StreetNumber
field and the rest into the StreetAddress field.

Any clever ideas how I might achieve this?

TIA,
Arthur
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com