Splitting data for labels

[Abay]

Hello ... I would like to create address labels from data in a table that
contains the full address in one field, each portion of the address is
separated by a comma. e.g. 123 Fir St., Vancouver, BC .. would like to
separate this into three separate fields by means of a query.

Any help would be much appreciated

Thanks in advance

Abay

 

[John Spencer]

Difficult to do accurately.

Does the address ALWAYS contain two commas - no more, no less?
If you just want to end up with
123 Fir St
Vancouver, BC

You could use the replace function (if you are using later versions of
Access) and get the one field into that format

Replace([AddressField],",",Chr(13) & Chr(10),1,1,1)
That will replace the first comma with carriage return and line feed to give
you two lines of information in the string

Otherwise you could look at the Instr, Mid, Left, Right functions to parse
the data into its parts.

I assume that the above is not the correct solution for you, since I would
guess that you have (a) a postal code and (b) multi-line addresses such as
123 Fir St, Suite 100, Vancouver, BC PostalCode

 

[Abay]

Many thanks for your suggestions .. I will try them out ..

Abay

Difficult to do accurately.

Does the address ALWAYS contain two commas - no more, no less?
If you just want to end up with
123 Fir St
Vancouver, BC

You could use the replace function (if you are using later versions of
Access) and get the one field into that format

Replace([AddressField],",",Chr(13) & Chr(10),1,1,1)
That will replace the first comma with carriage return and line feed to
give you two lines of information in the string

Otherwise you could look at the Instr, Mid, Left, Right functions to parse
the data into its parts.

I assume that the above is not the correct solution for you, since I would
guess that you have (a) a postal code and (b) multi-line addresses such as
123 Fir St, Suite 100, Vancouver, BC PostalCode

Hello ... I would like to create address labels from data in a table that
contains the full address in one field, each portion of the address is
separated by a comma. e.g. 123 Fir St., Vancouver, BC .. would like to
separate this into three separate fields by means of a query.

Any help would be much appreciated

Thanks in advance

Abay