Convert memo field to separate fields

[icsupt]

I have a Comments box that is a memo field. I would now like to separate this Comments field into a datasheet format.

The comments field is one big field and the user puts the date, then a dash and then the comments. To put in another date, they currently stay in the Comments field and press Ctrl-Enter i.e.
"12/10/04-This would be the second comment.
12/09/04-This would be the first comment."
This is currently in one big Comments memo field.

This is no longer a good format for me. So what I want to do now is wherever there is a date, I want to be able to copy it and put it in the datasheet format on separate lines. This will allow me to sort by date, filter, etc. So each date should be on its own line.

I made another field called CommentDS. What I'm doing now is copying each date's comments and then putting it one by one in a separate line in CommentDS.

I didn't think there was any easier way, but I thought I would ask anyway.

Is there an easier way?

Thanks in advance.

 

[willir]

Memo fields in Access are not really "fields" -- Access actually stores the outside of the table. This means that many things that can be done with date in a table can not be easily done with a memo field.

With Access 2000 an newer, you do have some functionaility. The first 255 characters are treated as a text string. This means this first 255 characters can be searched and manipulated. But the rest can not.

....sigh. I am with you on this. I have a similar issue I am wrestling with as a result of a poorly designed database.

Richard

 

[icsupt]

I was able to work around this somewhat. All of the comments in the database wrapped around. When there was a hard return, that was treated as a separate comment. My workaround is tedius, but it's better than copying each line separately on the clipboard.

First I made a new tab and put the Comments field down the bottom and the datasheet field at the top. Then I am going to each record and putting a temporary "x" in the datasheet for how many lines there are comments. So if the user had input 25 lines, I put an "x" in 25 rows in the datasheet fields. Then I copied all of the Comments memo field and then highlighted the 25 rows in the datasheet field. When I pasted them, they do go to separate rows. The date however also comes along so sometime in the future I'm going to take the dates and put them in a separate field.

I have over 1,000 records and the process is slow, but it is sure better than copying one at a time.