[AccessD] Numbers within a street

A query Column =VAL(StreetAddress) will give you the street number in all of those cases.  

If all you want is whether it is odd or evem, then just create a query column 
=VAL(StrretAddress) Mod 2

If you want the Street Name by itself , a first pass would be 
=RIGHT$(StreetAddress, INSTR(StreetAddress," ") + 1)

Note: In your third example, VAL() returns just the 2333.   If looking for the street name, I 
would want  "Queen Street", not "1070 Queen Street", so you lose data when splitting.  For 
the actual address, you'd use the original data rather that trying to put the Number and 
Streetname back together again. 

-- 
Stuart 

On 14 Sep 2011 at 16:31, Arthur Fuller wrote:

> 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
>