Welcome to SPN

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

Sign Up Now!

Data Conversion Manual Line Break

Discussion in 'Information Technology' started by JimMurray, Jul 28, 2006.

  1. JimMurray

    JimMurray
    Expand Collapse
    Guest

    I'm converting data from an old SQL back-end that has the Address field
    containing the street, city, state and zip AND Fields that contain the city,
    the State and the ZipCodes. I would like to parse out the citystatezip and
    just have the street address. There is a manual line break between the street
    and the CityStateZip in the Address field, but I cannot use it effectively. I
    have been using Edit - Replace to remove the information based on "city,*"
    but there are a lot of different citys and they are not accurately spelled.
    Any suggestions?
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Jim,

    first try creating a select query with the info you would
    like to keep

    on the grid, put

    field --> Address
    criteria --> Like "*" & Chr(13) & "*"

    field --> JustAddress:
    Left([address],InStr([address],Chr(13))-1)


    if this gets what you want, then you can make it an update query

    from the menu --> Query, Update

    in the UpdateTo cell under Addresses -->
    Left([address],InStr([address],Chr(13))-1)

    if you want the city, state, and zip, move them to another
    field before you strip them off

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    JimMurray wrote:
    > I'm converting data from an old SQL back-end that has the Address field
    > containing the street, city, state and zip AND Fields that contain the city,
    > the State and the ZipCodes. I would like to parse out the citystatezip and
    > just have the street address. There is a manual line break between the street
    > and the CityStateZip in the Address field, but I cannot use it effectively. I
    > have been using Edit - Replace to remove the information based on "city,*"
    > but there are a lot of different citys and they are not accurately spelled.
    > Any suggestions?
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Try using InStr the ASCII code for the manual line break to parse the data.

    "JimMurray" wrote:

    > I'm converting data from an old SQL back-end that has the Address field
    > containing the street, city, state and zip AND Fields that contain the city,
    > the State and the ZipCodes. I would like to parse out the citystatezip and
    > just have the street address. There is a manual line break between the street
    > and the CityStateZip in the Address field, but I cannot use it effectively. I
    > have been using Edit - Replace to remove the information based on "city,*"
    > but there are a lot of different citys and they are not accurately spelled.
    > Any suggestions?
    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    ps

    if chr(13) doesn't get anything, try chr(10)


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    strive4peace wrote:
    > Hi Jim,
    >
    > first try creating a select query with the info you would like to keep
    >
    > on the grid, put
    >
    > field --> Address
    > criteria --> Like "*" & Chr(13) & "*"
    >
    > field --> JustAddress: Left([address],InStr([address],Chr(13))-1)
    >
    >
    > if this gets what you want, then you can make it an update query
    >
    > from the menu --> Query, Update
    >
    > in the UpdateTo cell under Addresses -->
    > Left([address],InStr([address],Chr(13))-1)
    >
    > if you want the city, state, and zip, move them to another field before
    > you strip them off
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > JimMurray wrote:
    >
    >> I'm converting data from an old SQL back-end that has the Address
    >> field containing the street, city, state and zip AND Fields that
    >> contain the city, the State and the ZipCodes. I would like to parse
    >> out the citystatezip and just have the street address. There is a
    >> manual line break between the street and the CityStateZip in the
    >> Address field, but I cannot use it effectively. I have been using Edit
    >> - Replace to remove the information based on "city,*" but there are a
    >> lot of different citys and they are not accurately spelled. Any
    >> suggestions?
    >>
     

Share This Page