Append Data & Split Field into Three Fields

[Barry McConomy]

Hi

I want to append data from a table to another table, however, in Table1 the
telephone number (000) 111 2222 is in one field and in Table2 I want the
telephone number split in to 3 fields (with out the ( ) ), as follows:-

field1: 000
field2: 111
field3: 2222

Can somebody advise how I can do this.

Regards
Barry

 

[Mike Painter]

Hi

I want to append data from a table to another table, however, in
Table1 the telephone number (000) 111 2222 is in one field and in
Table2 I want the telephone number split in to 3 fields (with out the
( ) ), as follows:-
field1: 000
field2: 111
field3: 2222

Can somebody advise how I can do this.

You could use the mid function in an update query to place the values.

mid(OriginalField, 7,3) is 111 above.
mid(OriginalField,2,3) is 000

You could use these in a query as calculated fields so you could still use
the original number to dial from Access.

It would also be possible to write a little function using Split and Replace
to do the work.
If you are new to Access this would be a good place to learn a bit of
coding.

 

[Barry McConomy]

Mike

Thanks for the advice.

Regards
Barry

You could use the mid function in an update query to place the values.

mid(OriginalField, 7,3) is 111 above.
mid(OriginalField,2,3) is 000

You could use these in a query as calculated fields so you could still use
the original number to dial from Access.

It would also be possible to write a little function using Split and
Replace to do the work.
If you are new to Access this would be a good place to learn a bit of
coding.