Split address field

I have an [Address] field that has one (1) or two (2) lines.

i need to split current [Address] field into two (2) fields [AddressLine1]
and [AddressLine2]


Thank you

A bit more information needed...

If you were to tell a human assistant how to decide where to split that,
what would you tell them?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sandspur" wrote in message
...
I have an [Address] field that has one (1) or two (2) lines.

i need to split current [Address] field into two (2) fields [AddressLine1]
and [AddressLine2]


Thank you

Line1 of [Address] field would always end in a carriage return



"Jeff Boyce" wrote:

A bit more information needed...

If you were to tell a human assistant how to decide where to split that,
what would you tell them?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sandspur" wrote in message
...
I have an [Address] field that has one (1) or two (2) lines.

i need to split current [Address] field into two (2) fields [AddressLine1]
and [AddressLine2]


Thank you


.

On Sun, 14 Feb 2010 08:27:01 -0800, Sandspur
wrote:

I have an [Address] field that has one (1) or two (2) lines.

i need to split current [Address] field into two (2) fields [AddressLine1]
and [AddressLine2]


Thank you

AddressLine1: Left([Address] & Chr(13) & Chr(10),
InStr([Address] & Chr(13) & Chr(10), Chr(13) & Chr(10)) - 2)
AddressLine2: Mid([Address] & Chr(13) & Chr(10),
InStr([Address] & Chr(13) & Chr(10), Chr(13) & Chr(10)) + 2)

This appends a CrLf pair to the Address string (just in case there isn't one
in the string at all) and then uses the Left, Instr and Mid functions to
extract the desired portion (if there is one, AddressLine2 may well be a zero
length string if there's only one line).
--

John W. Vinson [MVP]