I have an Access 2003 DB where one of the columns contains multiple names
(10-15), seperated by special seperators that look like rectangular boxes.

I want to sperate each name so that each appears in individual row/column
alongside.

Kindly help.

Thanks,

A. Singh

Write a VBA function that opens 2 records:
- the source table, with the concatenated field;
- the target table that you want to add records to.

Use the Split() function to parse the elements into an array.
Loop through the array elements from LBound to Ubound.
For each one, use AddNew and Update to add the records to the target table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"A. Singh" <A. (E-Mail Removed)> wrote in message
news:A26DC0A8-B324-4DA8-80E5-(E-Mail Removed)...
>I have an Access 2003 DB where one of the columns contains multiple names
> (10-15), seperated by special seperators that look like rectangular boxes.
>
> I want to sperate each name so that each appears in individual row/column
> alongside.
>
> Kindly help.
>
> Thanks,
>
> A. Singh

Create a function in a module, in that example the seperator between the
names will be ~

Function MySplit(MyStr As String, MyLocation As Integer)
On Error goto MySplit_Err
MySplit = Split(MyStr, "~")(MyLocation)

Exit Function
MySplit_Err:
MySplit = ""
End Function

In the Query you can use this spit function to split the field

Select MySplit([FieldName],0) As FirstName, MySplit([FieldName],1) As
SecondName, MySplit([FieldName],2) As ThirdName From TableNAme


--
Good Luck
BS"D


"A. Singh" wrote:

> I have an Access 2003 DB where one of the columns contains multiple names
> (10-15), seperated by special seperators that look like rectangular boxes.
>
> I want to sperate each name so that each appears in individual row/column
> alongside.
>
> Kindly help.
>
> Thanks,
>
> A. Singh
>
>

Thanks.

A. Singh

"Allen Browne" wrote:

> Write a VBA function that opens 2 records:
> - the source table, with the concatenated field;
> - the target table that you want to add records to.
>
> Use the Split() function to parse the elements into an array.
> Loop through the array elements from LBound to Ubound.
> For each one, use AddNew and Update to add the records to the target table.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "A. Singh" <A. (E-Mail Removed)> wrote in message
> news:A26DC0A8-B324-4DA8-80E5-(E-Mail Removed)...
> >I have an Access 2003 DB where one of the columns contains multiple names
> > (10-15), seperated by special seperators that look like rectangular boxes.
> >
> > I want to sperate each name so that each appears in individual row/column
> > alongside.
> >
> > Kindly help.
> >
> > Thanks,
> >
> > A. Singh
>
>

use combinations of mid, left and righ functions to split out info. usiing
the "find" function to find a particular character that reoocurs in all the
data can help locate the starting character for the "mid" function. also the
"len" function can help to determine where to end or start a mid, left or
right function. help on these functions best found in google, ms help files
on functions are amazingly sparse and difficult to locate.
"Allen Browne" <(E-Mail Removed)> wrote in message
news:%23H%(E-Mail Removed)...
> Write a VBA function that opens 2 records:
> - the source table, with the concatenated field;
> - the target table that you want to add records to.
>
> Use the Split() function to parse the elements into an array.
> Loop through the array elements from LBound to Ubound.
> For each one, use AddNew and Update to add the records to the target
> table.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "A. Singh" <A. (E-Mail Removed)> wrote in message
> news:A26DC0A8-B324-4DA8-80E5-(E-Mail Removed)...
>>I have an Access 2003 DB where one of the columns contains multiple names
>> (10-15), seperated by special seperators that look like rectangular
>> boxes.
>>
>> I want to sperate each name so that each appears in individual row/column
>> alongside.
>>
>> Kindly help.
>>
>> Thanks,
>>
>> A. Singh
>