By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,700 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,700 IT Pros & Developers. It's quick & easy.

Parsing text from memo field

P: n/a
I have a memo field (in Access 2K) that I use to hold text versions of
emails. Here is an example:

---
From: Mickey Mouse
To: Minnie Mouse
Date: 7/21/04 1:10PM
Subject: Fwd: Topics for conference call

Thanks. That was the premise of Goofy's call earlier today.
---

I want to parse out the header data to respective fields (To, From,
Date, Subj). I've been all over the posts on this newsgroup today.
This is my first real "parse experience". Here's what I have so far
(this is to get the "To" name):

Dim fldMsg As String
Dim ToName As String
fldMsg = [Forms]![MESSAGES_ENTRY]![MESSAGE]

ToName = (Mid(fldMsg, ((InStr(1, fldMsg, "To:", 0)) + 4), _
(InStr((InStr(1, fldMsg, "To:", 0)), fldMsg, Chr(13),
vbBinaryCompare))))
MsgBox (ToName)

Now that you're done laughing at my code (just kidding), the only
purpose of the msgbox at this point is to show me what I have when I
run it. (For some reason whenever I try to run it from the code
window, it opens a Run Macro box with nothing in it. I have to
actually run this from the form) Ultimately, I would like to paste
the retrieved data into fields corresponding w/this message.

What I get when I run this is:

Minnie Mouse
Date: 7/21/04 1:10PM

It doesn't stop at the carriage return after "Mouse"...I've tried
various combinations of start positions (I even tried "1") and all it
ever does is change the position on the "Date" line where it stops
collecting the data...it never stops after "Mouse".

ANY help would be appreciated. Thanks!!!
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ds********@yahoo.com (Donna Sabol) wrote in message news:<e8**************************@posting.google. com>...
I have a memo field (in Access 2K) that I use to hold text versions of
emails. Here is an example:

---
From: Mickey Mouse
To: Minnie Mouse
Date: 7/21/04 1:10PM
Subject: Fwd: Topics for conference call

Thanks. That was the premise of Goofy's call earlier today.
---

I want to parse out the header data to respective fields (To, From,
Date, Subj). I've been all over the posts on this newsgroup today.
This is my first real "parse experience". Here's what I have so far
(this is to get the "To" name):

Dim fldMsg As String
Dim ToName As String
fldMsg = [Forms]![MESSAGES_ENTRY]![MESSAGE]

ToName = (Mid(fldMsg, ((InStr(1, fldMsg, "To:", 0)) + 4), _
(InStr((InStr(1, fldMsg, "To:", 0)), fldMsg, Chr(13),
vbBinaryCompare))))
MsgBox (ToName)

Now that you're done laughing at my code (just kidding), the only
purpose of the msgbox at this point is to show me what I have when I
run it. (For some reason whenever I try to run it from the code
window, it opens a Run Macro box with nothing in it. I have to
actually run this from the form) Ultimately, I would like to paste
the retrieved data into fields corresponding w/this message.

What I get when I run this is:

Minnie Mouse
Date: 7/21/04 1:10PM

It doesn't stop at the carriage return after "Mouse"...I've tried
various combinations of start positions (I even tried "1") and all it
ever does is change the position on the "Date" line where it stops
collecting the data...it never stops after "Mouse".

ANY help would be appreciated. Thanks!!!


Your memo looks like:

xxx<CR>
To: yyyy yyyy<CR>

You're looking for Mid(fldMsg, InStr(1,fldMsg, "To:",0))+4,
lengthofyyyys))

lengthofyyyys = PositionofCR - PositionofToPlus4 (you missed this
last term)

PositionofCR = InStr(InStr(1,fldMsg,"To:",0), fldMsg, Chr(13),
vbBinaryCompare)
PositionofToPlus4 = InStr(1,fldMsg,"To:",0) + 4)

So your final string becomes something like:

Mid(fldMsg, InStr(1,fldMsg, "To:",0))+4,
InStr(InStr(1,fldMsg,"To:",0), fldMsg, Chr(13), vbBinaryCompare) -
InStr(1,fldMsg,"To:",0) + 4)))

This should get you close enough to zero in on the problem.

James A. Fortune
Nov 13 '05 #2

P: n/a
Just an update....

It doesn't show in this newsgroup but I did receive a reply to this
message through DevelopersDex (thank you, by the way, to the person
who responded).

As usually happens for me, once I ask the question, my brain gets
offended and starts coming up with the answer on its own, rather than
waiting for someone to help. Here is what I came up with, and it's
working just fine. I thought I would post this for anyone who
searches for this kind of thing in the future:

Dim fldMsg As String
Dim FromStart As Integer
Dim ToStart As Integer
Dim DateStart As Integer
Dim SubjStart As Integer

Dim fldTO As String
Dim fldFROM As String
Dim fldDATE As Date
Dim fldSUBJECT As String

fldMsg = [Forms]![MESSAGES_ENTRY]![MESSAGE]
FromStart = (InStr(1, fldMsg, "From:", 0)) 'THE "START" LINES FIND
STARTING
ToStart = (InStr(1, fldMsg, "To:", 0)) 'POSITION FOR EACH PIECE OF
DATA
DateStart = (InStr(1, fldMsg, "Date:", 0))
SubjStart = (InStr(1, fldMsg, "Subject:", 0))

'THESE "ME!" LINES APPEND THIS DATA INTO THE APPROPRIATE FIELDS
Me!fldFROM = (Mid(fldMsg, (FromStart + 7), (ToStart - (FromStart +
7))))
Me!fldTO = (Mid(fldMsg, (ToStart + 4), (DateStart - (ToStart + 4))))
Me!fldDATE = (Mid(fldMsg, (DateStart + 7), (SubjStart - (DateStart +
7))))
Me!fldSUBJECT = (Mid(fldMsg, (SubjStart + 10), _
((InStr(SubjStart, fldMsg, Chr(13), 0)) - (SubjStart +
10))))

The problem I was having before (where it was passing the stop
position I was setting) was because MID needs the start position and
HOW MANY CHARACTERS AFTER. I was telling it the start position and
STOP POSITION. What I did above was find the difference between the
start & stop positions to give the number of characters.

Thanks everyone!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.