[AccessD] Numbers within a street

It will for Arthur's purpose.  
Val(234A....)   returns 234 which is on the Even side of the street.

There is no real difference between 234A and 2333-1070.  The initial numeric part (234, 
2333) identifies the building location, the rest just identifies something such as the apartment 
number within the building.


-- 
Stuart

On 15 Sep 2011 at 0:53, Asger Blond wrote:

> 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
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com