Comment field - Extracting Data

[phillib5]

Hello, I am working with an Access 97 database where the creator decided to have a comment field for every record. In this comment field, users have been entering in data over time . Now they want the report they print out for these records to only show the last couple lines of the comment that they have entered. Does anyone have any ideas on how this could be done?

Any help would be greatly appreciated, thank you!

 

[traingamer]

I'm assuming it is a memo field. You could try a query like

SELECT *,  "..." & Right([comment],50) AS lastComment
FROM tbl
WHERE ...;

which would return "..he quick brown fox jumps over the lazy dog. JONES" if that was the last 50 characters in the field.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[phillib5]

Thanks Greg, I may be able to make a variation of that work. Here is a sample of the end of a memo field I am working with....

2/3/05 lh will write and send coll assignment form to mr johnson…2/4/05 lh dicated letter to tape and to send coll assignement to mr stpieere to sign and setuo as 40% to law apartnership….
2/8/2005 - LH SENT OUT LETTER TO M. STPIERRE RE: COLLATERAL ASSIGNMENT -GM
2/23/05 LH ASKED GM TO SEND 2ND REMINDER ON LETTER SENT ON 2/3/05 TO MR STPOIERRE.I

What I'm looking for is the last 3 comments by date. The only order to these is that they start off each comment with a date. Perhaps I can try querying based on "/"? Will give it a go. Thanks again.

Bryant

 

[traingamer]

Is there a separator used to start each comment at the beginning of a line? If so you can probably parse on that.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[GingerR]

After this exercise, your next mission should be to make a Comments table, with a Date, Comment and UserID field, which is joined to your main table by an ID field (many comments for one existing record ID). Then users can enter a comment, you can plunk the date and userID in there automatically. All of your future searching woes will be taken care of.....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244