split address into 2 fields

[Guest]

I need help finding a way to do this. I have the addess in a field were addr1
is followed by a line feed then addr2 portion follows. I can identify the
portion after the line feed with:
SELECT [id] AS id, IIf(InStr([street],Chr(13) &
Chr(10))>0,Mid([street],InStr([street],Chr(13) & Chr(10))+2),"") AS street_2

But how can I put it all together to move addr1 into one field and addr2
into another field? What steps do I need to do?
eg:
"98 CROIX
APT 102"

Thanks for any ideas or advice.

 

[Guest]

Or, I would really just need to remove the line feed from within the address.
Is there a way to find/replace the Chr(10) or Chr(13) in the street column?
Thanks!

 

[Marshall Barton]

I need help finding a way to do this. I have the addess in a field were addr1
is followed by a line feed then addr2 portion follows. I can identify the
portion after the line feed with:
SELECT [id] AS id, IIf(InStr([street],Chr(13) &
Chr(10))>0,Mid([street],InStr([street],Chr(13) & Chr(10))+2),"") AS street_2

But how can I put it all together to move addr1 into one field and addr2
into another field? What steps do I need to do?
eg:
"98 CROIX
APT 102"

Use two calculated fields in the query:

SELECT id,
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10))), street)
As Addr1
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Mid(street, InStr(street, Chr(13) & Chr(10))+2), "")
As Addr2
From . . .

 

[Guest]

This worked good to split the addresses. But the line feed is still there.
Is there a way to remove the line feed? Eg results were:

2 "33 N PEARCE ST
" APT 8

There is a little symbol like a small square at the end of the T in ST.
I would like to see:
2 33 N PEARCE ST APT 8

Thanks a lot!

I need help finding a way to do this. I have the addess in a field were addr1
is followed by a line feed then addr2 portion follows. I can identify the
portion after the line feed with:
SELECT [id] AS id, IIf(InStr([street],Chr(13) &
Chr(10))>0,Mid([street],InStr([street],Chr(13) & Chr(10))+2),"") AS street_2

But how can I put it all together to move addr1 into one field and addr2
into another field? What steps do I need to do?
eg:
"98 CROIX
APT 102"

Use two calculated fields in the query:

SELECT id,
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10))), street)
As Addr1
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Mid(street, InStr(street, Chr(13) & Chr(10))+2), "")
As Addr2
From . . .

 

[Marshall Barton]

Sorry, it should be:

IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10)) - 1), street)
As Addr1
--
Marsh
MVP [MS Access]

This worked good to split the addresses. But the line feed is still there.
Is there a way to remove the line feed? Eg results were:

2 "33 N PEARCE ST
" APT 8

There is a little symbol like a small square at the end of the T in ST.
I would like to see:
2 33 N PEARCE ST APT 8

I need help finding a way to do this. I have the addess in a field were addr1
is followed by a line feed then addr2 portion follows. I can identify the
portion after the line feed with:
SELECT [id] AS id, IIf(InStr([street],Chr(13) &
Chr(10))>0,Mid([street],InStr([street],Chr(13) & Chr(10))+2),"") AS street_2

But how can I put it all together to move addr1 into one field and addr2
into another field? What steps do I need to do?
eg:
"98 CROIX
APT 102"

Use two calculated fields in the query:

SELECT id,
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10))), street)
As Addr1
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Mid(street, InStr(street, Chr(13) & Chr(10))+2), "")
As Addr2
From . . .

 

[Guest]

Thanks, that did it. And to see that additional -1 actually helped me
understand what's going on.

Sorry, it should be:

IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10)) - 1), street)
As Addr1
--
Marsh
MVP [MS Access]

This worked good to split the addresses. But the line feed is still there.
Is there a way to remove the line feed? Eg results were:

2 "33 N PEARCE ST
" APT 8

There is a little symbol like a small square at the end of the T in ST.
I would like to see:
2 33 N PEARCE ST APT 8

denise wrote:
I need help finding a way to do this. I have the addess in a field were addr1
is followed by a line feed then addr2 portion follows. I can identify the
portion after the line feed with:
SELECT [id] AS id, IIf(InStr([street],Chr(13) &
Chr(10))>0,Mid([street],InStr([street],Chr(13) & Chr(10))+2),"") AS street_2

But how can I put it all together to move addr1 into one field and addr2
into another field? What steps do I need to do?
eg:
"98 CROIX
APT 102"

Use two calculated fields in the query:

SELECT id,
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Left(street,InStr(street, Chr(13) & Chr(10))), street)
As Addr1
IIf(InStr(street, Chr(13) & Chr(10)) > 0,
Mid(street, InStr(street, Chr(13) & Chr(10))+2), "")
As Addr2
From . . .