Manipulating/Processing multiple lines in a memo field

[Nameless]

Hi:

I am using Access 2003 and in my table I have a memo field called fldmemo.
I append information (email body) from emails into this and everything works
perfectly except for some frustrating issues. I have NO influence on the
data/format going into the field so I have to manipulate the information
AFTER it goes into the field.

My first problem is that one of the lines has a funny symbol (square) and I
know it represents a tab. My question is, is it possible to replace/delete
this? Please take into account that I do not always know which line it is
on. It could be the 2 line, third or even forth line.

My second problem is I have many lines that are empty. Sometimes I have a
total of 4 blank lines before the next line actually contains information.
Is it possible to count blank lines and delete 1 or 2 of them?

My third problem is after I parse one of the lines to update another field,
it has the following information:
4000-1234-5678-1234 *
As you can see, the last character has an asterix ( * ) with some blank
lines. Because I do not know the exact number of numbers and dashes, is it
possible to just get the information and not the blank and/or asterix.

My final problem is that the field has multiple lines of crap at the very
end. This comes from signatures from emails that I do not need or want. I
know that one of the lines near the end has standard text (AA###). Is it
possible to delete any and all lines after the AA###? The ### represents
numbers.


Thank You

 

[Dirk Goldgar]

(answers inline)

Hi:

I am using Access 2003 and in my table I have a memo field called
fldmemo. I append information (email body) from emails into this and
everything works perfectly except for some frustrating issues. I
have NO influence on the data/format going into the field so I have
to manipulate the information AFTER it goes into the field.

My first problem is that one of the lines has a funny symbol (square)
and I know it represents a tab. My question is, is it possible to
replace/delete this? Please take into account that I do not always
know which line it is on. It could be the 2 line, third or even
forth line.

Use the Replace() function to replace Chr(9) with one or more spaces (or
such other character as may suit your purposes). Here's a basic VBA
example:

strEmailBody = Replace(strEmailBody, Chr(9), " ")

If you need to update existing data in a table, an update query like
this would do it:

UPDATE MyTable
SET EmailBody = Replace(EmailBody, Chr(9), " ");

My second problem is I have many lines that are empty. Sometimes I
have a total of 4 blank lines before the next line actually contains
information. Is it possible to count blank lines and delete 1 or 2 of
them?

One way is to use the Split() function to split the text on the vbCrLf
character combination, then reassemble the text from the array that
results, eliminating blank lines. You'd need to write a function to do
this.

My third problem is after I parse one of the lines to update another
field, it has the following information:
4000-1234-5678-1234 *
As you can see, the last character has an asterix ( * ) with some
blank lines. Because I do not know the exact number of numbers and
dashes, is it possible to just get the information and not the blank
and/or asterix.

You could use the Replace function once to replace all spaces with
zero-length strings (""), and again to replace the asterisk with a
zero-length string. Then you could use the Split function to split it
into an array, using the hyphen as a delimiter.

My final problem is that the field has multiple lines of crap at the
very end. This comes from signatures from emails that I do not need
or want. I know that one of the lines near the end has standard text
(AA###). Is it possible to delete any and all lines after the AA###?
The ### represents numbers.

If the "AA###" sequence is the start of a line, it would be easy, after
splitting the text into lines with the Split function, to check each
line to see if it is Like "AA###", and ignore that line and those after
it.