parse data from memo field to text field

[Guest]

I currently have a table in Access 2003 which contains case notes - each
record contains an ID# for the case, date and time fields, etc, and the
actual notes. The notes field is a memo field (currently I have no primary
key assigned to each individual note). I need to export this note data to a
table that will accept only text of 255 characters or less. The new table
will allow for an ID#,lineNo., and textfield.

Is there a way that I can create a query that would take each record from my
current table assign an ID#, then break up the memo field (case notes) into
255 characters sections assigning a line # to each section?

So then the text files can be concatinated(spelling?) in the line # order
when printed.

I hope someone out ther can help me!

P.S. I have very little programming experience, so I have limited
understanding of certain terms.

 

[Jeff Boyce]

Are you saying that each "note" in your current memo field is EXACTLY 255
characters long? Won't you get confusing "breaks" if you arbitrarily divide
the memo string into 255-character text strings?

How would you determine "lineNo"? You'll have to "explain" to Access how to
do that.

How will Access know that one "note" is finished and another one (i.e., new
ID#) needs to begin? You'll have to explain to Access...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff:
Thanks for the response - I was not clear enough - probably because I lack
the terminology-

My current table is as follows:
CLIENT# (6 digit ID number - a unique number assigned to the case), Date
(Date notes were entered), Counselor (the last name of counselor writing the
notes - a drop down list), Type of Activity (This is a drop down list, like
appointment, phone call made, phone call recieved, etc), Notes (this is my
memo field - the notes can be as little as 5 or so characters up to 1000's
(64K).)

The new system actually requires 2 tables:
Table#1- CaseNote: Client#, CasenoteID#(a unique identifier assigned to the
case), Note Type (a reference code), Date, CounselorID,

Table2- CaseNoteText: CasenoteID#(a unique identifier of the corresponding
case note), LineNo (A number assigned to each occurence of a note. This
sequence number determines in what order the note will be displayed),
CaseNote Text (Freeform text not to exceed 255 characters).

I have no difficulties creating Table1- CaseNote, it is table2 -that is
causing the stress - I can assign a caseNoteID#, etc - but how do I take the
contents of my Notes field?- count 255 charcters, assign to Line No 1, then
count the next 255 charcters and assign Line No 2, etc until the whole
contents has been broken into 255 character sections and assigned a line No.?

Does this clarify my objective?

 

[Jeff Boyce]

My original question still stands...

Are you COMPLETELY certain that you can safely divide the current memo field
into meaningful 255-character strings (and your answer better be NO, because
you just said "as little as 5 or so characters up to ...")?

Mechanically, yes, you can take a memo field and bite off chunks of 255
characters each, and assign a lineID ... but how will you (and Access) know
when one "note" ends and another begins? Unless you are saying that the
memo field is a SINGLE note (I had the impression that multiple notes were
embedded within the memo field).

You'll need to do some VBA programming via a procedure. The gist (i.e.,
untested aircode) of what you'll do is:

* grab the "next" record (starts w/ the first)
* note the recordID
* grab the memo field
* peel off the "next" 255 characters (starting at 0)
* assign the "next" LineNo (start at 0, so "next" is "1")
* write these to your new table
* as long as there are characters remaining, jump back up and peel off
the "next" 255 characters
* when there are no more characters in THIS record's memo field, jump
back to the top

Your code will need to continue cycling until there are no more characters
in the memo field of the last record.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Does this clarify my objective?

Just an example of what Jeff is asking: would you want your new table to
contain records with values like

Jeff:Thanks for the response - I was not clear enough - probably because I
lackthe terminology-My current table is as follows: CLIENT# (6 digit ID number
- a unique number assigned to the case), Date (Date notes were entered),
Counselor (the last name of

counselor writing thenotes - a drop down list), Type of Activity (This is a
drop down list, like appointment, phone call made, phone call recieved, etc),
Notes (this is my memo field - the notes can be as little as 5 or so
characters up to 1000's(64K).) T

he new system actually requires 2 tables: Table#1- CaseNote: Client#,
CasenoteID#(a unique identifier assigned to the case), Note Type (a reference
code), Date, CounselorID,Table2- CaseNoteText: CasenoteID#(a unique identifier
of the corresponding case no

te), LineNo (A number assigned to each occurence of a note. This sequence
number determines in what order the note will be displayed),CaseNote Text
(Freeform text not to exceed 255 characters). I have no difficulties creating
Table1- CaseNote, it is ta

ble2 -that is causing the stress - I can assign a caseNoteID#, etc - but how
do I take thecontents of my Notes field?- count 255 charcters, assign to Line
No 1, then count the next 255 charcters and assign Line No 2, etc until the
whole contents has been

broken into 255 character sections and assigned a line No.?

What you're literally asking is that the values break words in the middle,
wherever the 255 characters run out. Is that what you need, or do you want it
to break (where possible) at the end of sentences, or the end of words?

John W. Vinson [MVP]