Splitting data for labels

  • Thread starter Thread starter Abay
  • Start date Start date
A

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
 
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
 
Many thanks for your suggestions .. I will try them out ..

Abay



John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top