[AccessD] Numbers within a street

Arthur

I would sort the data first.

Then I would export it to Excel - your issue here will be the size of the
dataset although if you are using XL2007+ this is less of a hurdle

By having the data sorted you should be able to get the low hanging fruit
early. That is the "24 MyStreet Ave" and deal with them all pretty much in
one go.

You can then use the "Text to Columns" function to split the data.

If you do it in groups you might find you get a lot of it done easily.

Given how messy this sort of data is going to be, I feel eyeballing it
manually is going to be your best approach.  This will allow you to group it
into data chucks that you can then run a function or script over and get a
result.

Anyway. Good luck. It is usually a messy process as there are many
dissimilar valid combinations with address

Cheers
Darryl



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Thursday, 15 September 2011 6:31 AM
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