Re: clean up a field in table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 12/08/04 

Date: Wed, 8 Dec 2004 14:51:01 -0500

"Jack" <Jack@discussions.microsoft.com> wrote in message
news:C05D8091-E9E9-4E16-8365-263D94D1CC7D@microsoft.com
> Dirk,
> Thank you so much for the quick reply.

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!

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) 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • remove vbcrf,whitespace at a particular spot.
    ... I am having difficulty extracting the memo fields of a frontpage form that is ... I believe if I can remove all of the vbCrLf after ... info for memoadditional1 ... now becomes vbcrlf memoadditional1:) that way when I extract my data all I ...
    (microsoft.public.access.modulesdaovba)
  • Re: clean up a field in table
    ... kicker - Not only do they move it to the end, but it also inserts vbcrlf ... worse writes a book in one but leaves the other memofield empty. ... before memofield begins - that way data will extract fine. ... Public Function ExtractDetail(textLine As Variant, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Importing GAL
    ... Instead of limiting it to 2000 entries, I would like to extract "US" as ... > Const CdoAddressListGAL = 0 ... > Dim X As Variant, CDOList As Variant, TitleList As Variant, ... > Dim objSession As MAPI.Session, oFolder As MAPI.AddressList, ...
    (microsoft.public.excel.misc)
  • Re: clean up a field in table
    ... disregard first sentence - i am importing data into ACCESS 2003 not EXCHANGE ... > worse writes a book in one but leaves the other memofield empty. ... > before memofield begins - that way data will extract fine. ... > Public Function ExtractDetail(textLine As Variant, ...
    (microsoft.public.access.modulesdaovba)
  • extracting data
    ... I am using the following routine to extract data from emailform. ... Public Function ExtractDetail(textLine As Variant, ... Dim StartLine As Variant, EndLine As Variant, ExtractText As Variant ...
    (microsoft.public.access.modulesdaovba)