Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping Notes (v2K)

    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!!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping Notes (v2K)

    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.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Notes (v2K)

    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?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Notes (v2K)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trapping Notes (v2K)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •