Reversing Sentence Order in Memo Field

[autocutter]

I was given a tough one to crack and need some ideas and or snippets. Using an Access ADP and SQL 2000, we have a memo field were everyone enters their notes. There is single memo field per client located on the master client table. By the orginal design the notes were to be entered from oldest to newest; with the newest enter added to the bottom of the field.

The note process begins with finding the last used line in the memo field then a chr(13)chr(10) is added to start a new line. The notes are then formatted at the beginning of each entry with the current date, a " - ", the persons full name and another " - ".

example:

11/17/2002 - Joe Blow - This is a note
11/18/2002 - Joe Smith - this is a note, too.

There is no clear space between the individual notes, the note may wrap several times or the note may have additional chr(13)chr(10)'s added by the user. Finally, the note may or may not end with a period.

The question being asked is can I programaticly reverse the order of these notes, i.e. Newest to the Oldest via the SQL. I don't have a clue about a foolproof way to do this and I am open to any ideas that will be right at least 90% of the time.

 

[mwolf00]

You could write a stored procedure that loads on record at a time and parses the memo field. Go through the field looking for a chr(13)chr(10) followed by a date followed by a "-". Find the first one and second one and take what's inbetween and add it to a new variable. As you find more, add them to the begining of the variable...

SELECT @newMemo = @newPart + @newMemo

and parse the whole string...

YUCK!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

I would do something like this in an ASP page actually - since you can create an array of memo field entries to make processing much faster...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)