split address into 2 fields

D

denise

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.
 
D

denise

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!
 
M

Marshall Barton

denise said:
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 . . .
 
D

denise

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!


Marshall Barton said:
denise said:
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 . . .
 
M

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

denise said:
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"

Marshall Barton said:
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 . . .
 
D

denise

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

Marshall Barton said:
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"

Marshall Barton said:
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 . . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top