[AccessD] Numbers within a street

But VAL wouldn't catch the street number 234A.
A function like this might do the work:

Function StreetNumber(Address As String) As String
    Dim strStreetNumber AS String
    strStreetNumber = Left(Address, InStr(Address, " "))
    If InStr(Address, "-") > 0 Then
        strStreetNumber = Left(Address, InStr(Address, "-") - 1)
    End If
    StreetNumber = strStreetNumber
End Function

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 14. september 2011 23:44
Til: Access Developers discussion and problem solving
Emne: Re: [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
> 



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