Splitting field into separate excel cells.

[Iona via AccessMonster.com]

Hi all,

If anyone had any suggestions.. I have an access database for contact details.
. The underlying tables are split into Name, Address. When inputting the
address, the user separates the lines by hitting return:
Address1, (return) Address2, (return) Address3, (return) etc.

I also export to excel, but I need to split the Address field into separate
excel cells where the user has separated by 'return'. Can I set this up in
access?

Can anyone help?

Thanks.

 

[John Vinson]

Hi all,

If anyone had any suggestions.. I have an access database for contact details.
The underlying tables are split into Name, Address. When inputting the
address, the user separates the lines by hitting return:
Address1, (return) Address2, (return) Address3, (return) etc.

I also export to excel, but I need to split the Address field into separate
excel cells where the user has separated by 'return'. Can I set this up in
access?

I'd strongly suggest having these in separate fields in Access in the
first place! As you have found, it's much easier to put things like
this together than it is to split them apart consistantly.

Try using the built-in Split() function. The problem will be that
there are surely variable numbers of lines in addresses, and
inconsistancies in how those lines are used - e.g. is the PostCode on
a line by itself or on the same line as the City?

Address1: Split([Address], 1)
Address2: Split([Address], 2)
<etc>


John W. Vinson[MVP]

 

[Iona via AccessMonster.com]

Hi John,

Thanks for the input

Of course if I knew beforehand, id have structured the fields differently,
but naturally, the end user appears a month later asking for the different
excel!!! And without restructuring every other query and form I have, I just
need to change the excel output... a headache to be sure!

Thanks for the suggestion, could u go into more detail? Im still learinng
access

appreciated.

Hi all,

[quoted text clipped - 6 lines]

excel cells where the user has separated by 'return'. Can I set this up in
access?

I'd strongly suggest having these in separate fields in Access in the
first place! As you have found, it's much easier to put things like
this together than it is to split them apart consistantly.

Try using the built-in Split() function. The problem will be that
there are surely variable numbers of lines in addresses, and
inconsistancies in how those lines are used - e.g. is the PostCode on
a line by itself or on the same line as the City?

Address1: Split([Address], 1)
Address2: Split([Address], 2)
<etc>

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the suggestion, could u go into more detail? Im still learinng
access

Create a Query based on the table. In as many vacant Field cells as
you have lines in the addresses, type

Address1: Split([Address], 1)

using your actual fieldname, for values 1, 2, 3, ... etc.

John W. Vinson[MVP]