Data Conversion Manual Line Break

#1 (**permalink**) 28-Jul-2006, 08:28 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12410-data-conversion-manual-line-break.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410
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 (**permalink**) 28-Jul-2006, 08:28 AM

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

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410

remote programming and training
strive4peace2006 at yahoo.com

*

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410
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?
>

#3 (**permalink**) 28-Jul-2006, 08:28 AM

Try using InStr the ASCII code for the manual line break to parse the data.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410

"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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410
> 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 (**permalink**) 28-Jul-2006, 08:28 AM

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

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410
>
> remote programming and training
> strive4peace2006 at yahoo.com
>
> *
>
> JimMurray wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12410
>
>> 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?
>>