Split the Memo field

[Roy]

I have a table with two fields. I wish to separate all the data in
one field and keep it listed against the information in the other
field. The information within the field TicketStatus, I wish to
separate is
separated with a small box, meaning a tab .There can be up
to twenty separate bits of info in the field.
I would like to get a table in which the information in the field I
am trying to separate (TicketStatus),is individually listed against
the info in the
other field. I am using Access 2000

eg of entry in TicketStatus:
02/01/2007 14:23:35 EDT: Problem Closed. tab 01/04/2007 09:05:31 EDT:
PD Report Published.
tab 01/03/2007 10:37:38 EDT: Problem opened. tab 01/03/2007 12:00:24
EDT: Problem updated.

I have written the tab for illustartion purposes,but you will
understand the same.

Any Suggestions?

Roy

[Gord]

On Mar 4, 9:59 am, "Roy" <[e-mail]:

I have a table with two fields. I wish to separate all the data in
one field and keep it listed against the information in the other
field. The information within the field TicketStatus, I wish to
separate is
separated with a small box, meaning a tab .There can be up
to twenty separate bits of info in the field.
I would like to get a table in which the information in the field I
am trying to separate (TicketStatus),is individually listed against
the info in the
other field. I am using Access 2000

eg of entry in TicketStatus:
02/01/2007 14:23:35 EDT: Problem Closed. tab 01/04/2007 09:05:31 EDT:
PD Report Published.
tab 01/03/2007 10:37:38 EDT: Problem opened. tab 01/03/2007 12:00:24
EDT: Problem updated.

I have written the tab for illustartion purposes,but you will
understand the same.

Any Suggestions?

Roy

We'll call your existing table [tblOld] and assume its structure is:

TicketNo: Long Integer
TicketStatus: Memo

Create a new table called [tblNew] with the following structure:

TicketNo: Long Integer
TicketStatusItem: Text(255)

The following code will split the memo field into individual items:

Sub SplitMemo()
Dim rstOld As DAO.Recordset, rstNew As DAO.Recordset
Dim items As Variant, thing As Variant

Set rstOld = CurrentDb.OpenRecordset( _
"SELECT TicketNo, TicketStatus FROM tblOld", _
dbOpenSnapshot)

Set rstNew = CurrentDb.OpenRecordset( _
"tblNew", _
dbOpenTable)

Do While Not rstOld.EOF
items = Split(rstOld!TicketStatus, vbTab, , vbTextCompare)
For Each thing In items
rstNew.AddNew
rstNew!TicketNo = rstOld!TicketNo
rstNew!TicketStatusItem = thing
rstNew.Update
Next
rstOld.MoveNext
Loop

rstNew.Close
Set rstNew = Nothing
rstOld.Close
Set rstOld = Nothing
End Sub