clean up a field in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have field in a table that needs to cleaned up. I am wanting to remove all
of the chr(10) and chr(13) and vbcrf after a 'keyword' inside of field. The
field holds a body of email. The 'keyword' is distintive and is only used once

I then would like to insert a chr(13) to a 'keyword' within the same scope.

Any help would be greatly appreciated
 
Jack said:
I have field in a table that needs to cleaned up. I am wanting to
remove all of the chr(10) and chr(13) and vbcrf after a 'keyword'
inside of field. The field holds a body of email. The 'keyword' is
distintive and is only used once

I then would like to insert a chr(13) to a 'keyword' within the same
scope.

Any help would be greatly appreciated
Click to expand...

An update query that uses the Replace() function to fix up the field
seems like a reasonable approach, if you're using Access 2000 SP3(?) or
later. (It may be SP1 or 2 -- as first released, you couldn't call the
Replace function directly from a query, but one of those service packs
fixed it.)

It's not quite clear to me exactly what you want to replace, but here's
one example:

UPDATE MyTable SET MyField =
Replace(MyField,
"MYKEYWORD" & Chr(13) & Chr(10),
"MYKEYWORD")

That will remove a CR+LF combination that immediately follows
"MYKEYWORD".
 
Dirk,
Thank you so much for the quick reply. I am trying to import emails into
exchange. The bodys consists of frontpage forms. I am trying to extract that
data into access database - each field from form is extracted. My code looks
for 'keyword' AND a vbcr or a chr(13) - any data between is trim and inserted
in database. As I mentioned before the SINGLE fields (first_name,last_name,
etc)extract fine. The problem is that for some unknown reason Frontpage's
form processor moves any memoboxes to the end of the email. HERE is the
kicker - Not only do they move it to the end, but it also inserts (2) vbcrlf
between the last single field(next field)AND (2) vbcrlf at the START of data
for that field. It gets real ugly if user uses memobox to write a book or
worse writes a book in one but leaves the other memofield empty.
My database is ready to go EXCEPT these dreaded memo fields. Any help would
be so ever greatful!!!

Here is my email sample:
first_name: jack
last_name: bright


memofield1:

here is start of data
it may continue and
continue

memofield2:


memofield3:

notice field2 no data
still has the (2)vbcrlf between

I was thinking I could replace all of the vbcrlf at the start where
memofields begin and replace with "" then do another to insert one right
before memofield begins - that way data will extract fine.

here is code I use to extract:


firstname = ExtractDetail(rstExchange!Body, "firstname:")
memofield1 = ExtractDetail(rstExchange!Body, "memofield1:" & vbCrLf & vbCrLf)
**notice the vbcrlf & vbcrlf - this DOES extract the first line of text / or
until it hits a chr(13) within the body of memofield.

____________________ here is function to extract

Public Function ExtractDetail(textLine As Variant, FormItemReq As String) As
Variant
Dim StartLine As Variant, EndLine As Variant, ExtractText As Variant

StartLine = InStr(textLine, FormItemReq)
If StartLine > 0 Then

StartLine = StartLine + Len(FormItemReq)
EndLine = InStr(StartLine, textLine, vbCrLf)
ExtractText = Mid(textLine, StartLine, EndLine - StartLine)

End If
If Len(ExtractText) = 0 Then ExtractText = ""

ExtractDetail = Trim(ExtractText)

End Function

I have been struggling for weeks on this. I have asked numerious and have
yet to find a solution to this. If I going about this the wrong way or if you
can suggest a better way I would be really happy!
 
Jack said:
Dirk,
Thank you so much for the quick reply.
Click to expand...

Unfortunately, I couldn't reply to this one as quickly.
I am trying to import emails
into exchange. The bodys consists of frontpage forms. I am trying to
extract that data into access database - each field from form is
extracted. My code looks for 'keyword' AND a vbcr or a chr(13) - any
data between is trim and inserted in database. As I mentioned before
the SINGLE fields (first_name,last_name, etc)extract fine. The
problem is that for some unknown reason Frontpage's form processor
moves any memoboxes to the end of the email. HERE is the kicker - Not
only do they move it to the end, but it also inserts (2) vbcrlf
between the last single field(next field)AND (2) vbcrlf at the START
of data for that field. It gets real ugly if user uses memobox to
write a book or worse writes a book in one but leaves the other
memofield empty.
My database is ready to go EXCEPT these dreaded memo fields. Any help
would be so ever greatful!!!

Here is my email sample:
first_name: jack
last_name: bright


memofield1:

here is start of data
it may continue and
continue

memofield2:


memofield3:

notice field2 no data
still has the (2)vbcrlf between

I was thinking I could replace all of the vbcrlf at the start where
memofields begin and replace with "" then do another to insert one
right before memofield begins - that way data will extract fine.

here is code I use to extract:


firstname = ExtractDetail(rstExchange!Body, "firstname:")
memofield1 = ExtractDetail(rstExchange!Body, "memofield1:" & vbCrLf &
vbCrLf) **notice the vbcrlf & vbcrlf - this DOES extract the first
line of text / or until it hits a chr(13) within the body of
memofield.

____________________ here is function to extract

Public Function ExtractDetail(textLine As Variant, FormItemReq As
String) As Variant
Dim StartLine As Variant, EndLine As Variant, ExtractText As Variant

StartLine = InStr(textLine, FormItemReq)
If StartLine > 0 Then

StartLine = StartLine + Len(FormItemReq)
EndLine = InStr(StartLine, textLine, vbCrLf)
ExtractText = Mid(textLine, StartLine, EndLine - StartLine)

End If
If Len(ExtractText) = 0 Then ExtractText = ""

ExtractDetail = Trim(ExtractText)

End Function

I have been struggling for weeks on this. I have asked numerious and
have yet to find a solution to this. If I going about this the wrong
way or if you can suggest a better way I would be really happy!
Click to expand...

Here is a modified version of the function that you may serve. The
trouble is that the input data isn't entirely regular.

'------ start of code ------
Public Function ExtractDetail( _
textLine As Variant, _
FormItemReq As String, _
Optional Dlm As String = vbCrLf) _
As Variant

Dim StartLine As Long, EndLine As Long, ExtractText As String

StartLine = InStr(textLine, FormItemReq)
If StartLine > 0 Then

StartLine = StartLine + Len(FormItemReq)
EndLine = InStr(StartLine, textLine, Dlm)
ExtractText = Mid(textLine, StartLine, EndLine - StartLine)

While Left(ExtractText, 2) = vbCrLf
ExtractText = Mid(ExtractText, 3)
Wend

End If

ExtractDetail = Trim(ExtractText)

End Function

'------ end of code ------

Note the additional, optional argument "Dlm". You need to specify this
differently for the memo fields. This is how I tested:

strFirstName = ExtractDetail(strEmail, "first_name:")

strLastName = ExtractDetail(strEmail, "last_name:")

strMemo1 = ExtractDetail(strEmail, _
vbCrLf & "memofield1:" & vbCrLf, _
vbCrLf & vbCrLf)

strMemo1 = ExtractDetail(strEmail, _
vbCrLf & "memofield2:" & vbCrLf, _
vbCrLf & vbCrLf)

strMemo1 = ExtractDetail(strEmail, _
vbCrLf & "memofield3:" & vbCrLf, _
vbCrLf & vbCrLf)
 
YOU ARE AWESOME!!!!!!!!!!!!!!! THAT WORKED PERFECT! If you are ever in the NC
area, look around for a guy with only half of his head covered with hair (I
was begining to start pulling that side out too!). He owes you BIG
time!!!!!!!!. I really do appreciate your time and expertise. Its people like
you that make these boards a success!!!!!!!!!!!!!!!! May I ask you what the
logic behind code does - so that I can save the other half hair headed out
there. I dont get the 2 'extra' vbcrlf (one before and 3 after strmemo -
before extract). I also dont get the line before while statment or the while
process. Either way thank you, thank you, thank you (one for each memo :) )
Jack
 
[... thank you snipped for humility ...]

You're welcome. said:
May I ask you what the logic behind code does
- so that I can save the other half hair headed out there. I dont get
the 2 'extra' vbcrlf (one before and 3 after strmemo - before
extract). I also dont get the line before while statment or the while
process.
Click to expand...

Well, let's see. For safety's sake, I changed the calling logic so that
the memo fields are identified by a CR+LF, followed by the fieldname
tag, followed by a CR+LF. That was where before you were looking for
the fieldname, followed by CR+LF+CR+LF. That change was just to reduce
the possibility of encountering an apparent fieldname tag in the midst
of the text of another field, just by happenstance.

I changed the routine so that it would accept an optional delimiter to
mark the end of the field's text. The default is just to use a CR+LF,
and that works fine for the simple text fields, but not for the memo
fields -- for them, we need to use two CR+LFs in a row as the delimiter.

Because of the way I'm parsing out the memo fields, the text of the
field may end up with a leading CR+LF -- usually will, unless the field
is empty. I decided that it would make sense to strip any leading
CR+LFs off the front of the returned text. That's what that While loop
does -- it keeps stripping off leading CR+LFs until there aren't any
more.
 
Back
Top