Replace Part of Memo Text 1

[DugyWugy]

Hello,

I'm wondering if anyone has some insight to this issue:
I would like to replace some characters in the text within a memo field.
Here is an example of the data:
Call Date: 08/01/2006 Call Recorded: 07:31:25 AM
Contact Name: John Doe
Time to Call: 07:00-08:00

I would like to replace the ":" sign in "07:00-08:00" with "_".
The reason is, that I'm parsing through the memo field and using the ":" signs as delimiters. The issue with the "Time to Call:" entry is that data can be entered other ways ie.
Time to Call: 7-8
Time to Call: 9am-5pm
...
The rest of the structure in the memo is fine. I'm wondering if it is possible to search for a set number of characters after a string and do a replace? For example: replace any instances of ":" within 15 characters after "Time to Call:".

Your thoughts and help will be appreciated.
Thanks,

 

[rjoubert]

Is the Time To Call value always going to be at the end of the field?

 

[rjoubert]

Either way, this is what I came up with. You may have to tweak the numbers a bit to get it to work for all of your records. Create your update query, and update your memo field to the following (complex) expression...

(Left([YourTable]![YourMemoField],InStr(1, [YourTable]![YourMemoField],"Time To Call", 1) + 13)) & (Replace((Mid([YourTable]![YourMemoField], InStr(1,[YourTable]![YourMemoField], "Time To Call", 1) + 13, 30)), ":", "_"))

Good Luck!

 

[DugyWugy]

Hi rjoubert,

Thanks for the tip!
No, the Time To Call is actually part way through the memo. I've used your example and it's getting the results I want for the section I need to update. However, it deletes the rest of the data after the section we replace. Is there a way to avoid deleting the rest of the data?

Thanks in advance!

 

[rjoubert]

Like I said, you'll probably have to tweak the numbers a bit...add this to the end of the expression...

(Mid([newtable2]![Data], InStr(1, [newtable2]![Data], "Time To Call", 1) + 30, Len([newtable2]![Data])))

This should give you the rest of your memo field.