Data Conversion Manual Line Break

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

 

[strive4peace]

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

*

 

[Guest]

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

 

[strive4peace]

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

*

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

*

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?