HELP! - How to parse memo field?

[vongthep]

NEED HELP!
I have an Access table with 2 fields, both are type memo. Let's say they are named FieldA and FieldB. In fieldA there is text and paragraphs are represented by %##%%##%. Let's say I want to copy only the first 2 paragraphs in a record in FieldA and copy that into a record in FieldB. Also I want to check in FieldB if the text already exists and if it does delete it and update it with the new text.

How can I do that? thanks for your help!

Here's what my table named Test looks like:

FieldA (type memo)
FieldB (type memo)

Example of text in FieldA:



code:--------------------------------------------------------------------------------
Hello world.%##%%##%I like VB.%##%%##%This forum is helpful.%##%%##%Thanks for you help. --------------------------------------------------------------------------------


Paragraphs are denoted by %##%%##%. So if I want to copy the first 2 paragraphs in all the records in FieldA into FieldB, FieldB records would look like:


code:--------------------------------------------------------------------------------Hello world.%##%%##%I like VB.%##%%##% --------------------------------------------------------------------------------


I convert the %##%%##% into Chr(13) & Chr(10) after the copy.

 

[DALeffler]

If FieldA can be assured to have at least 2 paragraph delimiters ("%##%%##%"), try this query on your table:

SELECT tblMemos.FieldA, Mid([FieldA],1,InStr(InStr(1,[FieldA],"%##%%##%")+8,[FieldA],"%##%%##%")+7) AS Expr1
FROM tblMemo;

Note: If FieldA has less than 2 paragraph delimiters, the first 7 characters of FieldA will be returned in Expr1. Those records can be excluded in the query with a WHERE clause:

SELECT tblMemos.FieldA, Mid([FieldA],1,InStr(InStr(1,[FieldA],"%##%%##%")+8,[FieldA],"%##%%##%")+7) AS Expr1
FROM tblMemo
WHERE (((Len(Mid([FieldA],1,InStr(InStr(1,[FieldA],"%##%%##%")+8,[FieldA],"%##%%##%")+7))>7)=True));

Any more than that and I'd say you'll need a custom function call for the query...

hth,