By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

Split the Memo field

P: n/a
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

Mar 4 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Mar 4, 9:59 am, "Roy" <praish1...@yahoo.comwrote:
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

Mar 4 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.