Trapping Notes (v2K)

Topic

New Reply

I created a memo field in a database to serve as a diary of notes/activity/etc. I have a report and I want to capture the notes from this field. However, I don’t want all the notes as some of them are very lengthy. I want to capture only the most recent notes at the end of the field. My current solution is to use the “right([memofield],500)” function to capture the most recent 500 character string of notes.

My problem is the 500 characters might start in the middle of a sentence and that looks a bit funny on a report. I would like the string to start after a period or after ‘Ctrl-Enter’ as most of the users break journal entries using ‘Ctrl-Enter’.

Any thoughts would be greatly appreciated.
Thanks!!

Reply | Quote

Replies

Your big problem is that you’re shovelling everything into a single memo field and then tying yourself in knots trying to pull only part of it out. You could have saved yourself a lot of pain by putting your notes into a separate table with 3 or 4 fields in it, a field for the parent record’s ID as a foreign key, a field for the date of the note, and the memo field to hold that particular note. You might also want a field for a sequence number unless you use date *and* time in your date field. Then you could simply pull the note with the latest date/time or the highest sequence number out of that table instead of trying to extract a piece of a single memo field.

Reply | Quote

Initially, this field was to serve as nothing more than a spot to capture random thoughts (as defined by the end-users). It was never intended to be used in reports and people wanted it as simple as possible to jot notes. Now, the need has changed and I have to find a way to make it work on a report.

Do you know of any code that might help me?

Reply | Quote

Hi Charlotte,
I think I finally uncovered a solution:
Trim(Right([memotest],28-InStr(1,Right([memotest],30),”.”)))
30 is the length of string I want to extract. I used 28 to account for the space between the “.” and the character occupied by the ‘Ctrl-Enter’ (Is there a way to represent this character in an expression?).
As always, thanks for the help.

Reply | Quote

Ctrl+Enter actually inserts 2 characters: Carriage Return (ASCII 13) and Line Feed (ASCII 10). Visual Basic has symbolic constants vbCr =Chr(13), vbLf = Chr(10) and vbCrLF = Chr(13) & Chr(10). You can use these in InStr to find the position of the line end.

Reply | Quote