Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update Query

Discussion in 'Information Technology' started by Roger Bell, Nov 3, 2005.

Tags:
  1. Roger Bell

    Roger Bell
    Expand Collapse
    Guest

    Is there any way I can design an Update query or other query type to transfer
    street numbers which form part af an address field: EG: 21 Smith Street to a
    new field called Street Number and then Delete just the Street number from
    the existing address field.

    New fields would appear as: Street Number : 21; Street Name: Smith Street

    Thanks for any help
     
  2. Loading...


  3. Dennis

    Dennis
    Expand Collapse
    Guest

    How consistent is your data ?
    Does every address field start with a street number and then a space to the
    street name ?
    Is the number and name in the same field every time ?
    If it is not consistent every time, provide a couple of examples where there
    is not a number and a street name.

    "Roger Bell" wrote:

    > Is there any way I can design an Update query or other query type to transfer
    > street numbers which form part af an address field: EG: 21 Smith Street to a
    > new field called Street Number and then Delete just the Street number from
    > the existing address field.
    >
    > New fields would appear as: Street Number : 21; Street Name: Smith Street
    >
    > Thanks for any help
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 2 Nov 2005 00:00:04 -0800, "Roger Bell"
    <RogerBell@discussions.microsoft.com> wrote:

    >Is there any way I can design an Update query or other query type to transfer
    >street numbers which form part af an address field: EG: 21 Smith Street to a
    >new field called Street Number and then Delete just the Street number from
    >the existing address field.
    >
    >New fields would appear as: Street Number : 21; Street Name: Smith Street
    >
    >Thanks for any help


    An answer... and a warning.

    You can do this with an Update query by adding *two* new fields to
    your table - StreetNumber and StreetName. Update StreetNumber to

    Left([Address], InStr([Address], " ") - 1)

    and StreetName to

    Mid([Address], InStr([Address], " ") + 1)

    The warning: I've seen addresses like

    312 1/2 Hickory Lane => Street becomes "1/2 Hickory Lane"
    225 A Maple St. => Street becomes "A Maple Street"
    Ballacraine => Generates an error

    John W. Vinson[MVP]
     
  5. Roger Bell

    Roger Bell
    Expand Collapse
    Guest

    Thank you Dennis,
    Yes, in the main, the address field does start with a street number and a
    space to the street name. On the very odd occasion the street number is
    missing and just shows the street name EG:Lot 14 Smith Street, however, the
    street number field is currently blank in all cases.

    Any further advice would be appreciated, as I inherited the data base this
    way and would like to split the address as described above.

    "Dennis" wrote:

    > How consistent is your data ?
    > Does every address field start with a street number and then a space to the
    > street name ?
    > Is the number and name in the same field every time ?
    > If it is not consistent every time, provide a couple of examples where there
    > is not a number and a street name.
    >
    > "Roger Bell" wrote:
    >
    > > Is there any way I can design an Update query or other query type to transfer
    > > street numbers which form part af an address field: EG: 21 Smith Street to a
    > > new field called Street Number and then Delete just the Street number from
    > > the existing address field.
    > >
    > > New fields would appear as: Street Number : 21; Street Name: Smith Street
    > >
    > > Thanks for any help
    > >
     

Share This Page