Extract specific text from very long string

[csmith]

@aytee11

Could you walk me through this bit of code and how/where I would need to put it for it to run. Sorry to be so new but I am. Thanks! Really appreciate such a robust response from the community.

[Micron]

Let's not forget that the original wish was to do this in some sort of query. What I have so far is this, which should work from a query as I mentioned.

Function ExtractStrings(FldName As String, lngID As Long) As String
'FldName comes from query calculated field name; lngID is the autonumber ID of the record
Dim strText As String
Dim lngPos As Long, lngPos2 As Long
Dim strFldValue As String

strText = DLookup("TextValue", "tblExtractText", "ID = 1")
lngPos = 1
lngPos = InStr(lngPos, strText, FldName) + Len(FldName)
'lngPos = InStr(lngPos, strText, ":")'finds first colon at 9
lngPos2 = InStr((lngPos + 1), strText, Chr(10))
strFldValue = Mid(strText, lngPos, 9)'just testing. 9 would become position of lngPos2 (roughly)
MsgBox strFldValue
End Function

It is incomplete and I haven't nailed down finding the crlf that follows whatever field name is passed to it. As I mentioned, I figured out how to do something similar from an Update query elsewhere and believe this will work in an Append query as well. If csmith would rather work with recordsets and tables, that's perfectly fine of course, but I don't think I will try to finish unless I'm asked to. There's so many solutions being proposed and I wouldn't want to spend time on something that isn't needed.

[csmith]

@micron

Could you post a screen shot of the query design view with what you've explained here? Thanks!

[aytee111]

For my bit, you would first change the code to include all the fields in the string, not just the first 6 like I have done. Create a table with those exact field names (what I called Table10). Change Table 9 to be the table where this data is stored, change Field1 to be the field name where this data is.

This is a routine that is stand alone, it's job is to extract each bit of data and put them on to a table as individual fields. You can attach it to a button ("Call ExtractData()") or get a macro to run it. Go into VBA editor and insert a new module, then copy and paste the above code.

[csmith]

@aytee111

Thanks! I've gone in to the VBA editor and created a module, modified the code, created a table, saved and named it... how do I make it run? I want it to run automatically/in real time as info comes into the database via the form that is feeding in. Thanks!

[aytee111]

Well done for getting that done, I'm impressed! No idea how to make it run in what you described, I think you should start a new thread, explain in more detail what is going on in the database when you want the macro to run.

[aytee111]

Hope you tested it thoroughly! My suggestions don't come with a guarantee

[Micron]

Making assumptions based on your post. Your data looks like this

The query design grid (showing only a field to get the LastName)

The function (already posted but unfinished) takes "LastName:" including the colon, from the query calculated field. The intent would be to get the characters after LastName: up to the first crlf that follows and return that to the query. You want "FirstName" too? Add First_Name: ExtractStrings("FirstName:",[ID]) to the query grid. The function now looks for "FirstName:" and repeats the process. With this, no need to remember or calculate a position based on the last piece found - the starting marker becomes whatever your field name is (e.g. State: ). If your label names are not consistent every time; i.e. you have LastName from one source, Last Name or LastName- or LastName; from another, you only need a separate query with those field names in the grid.

That is my theory. It would take a bit of time to prove or disprove it.

[orange]

Not sure if this is helpful, but I substituted a "$" for each vbCrLf and found the following on the test record.

Field names from your form are listed below followed by a colon and 
what the$user entered for a response.$$$LastName:$W*****$$$$$FirstName:$D****$$$$$MI:$$$$$$$
State:$CA$$$$$Street:$63**** ***Ct$$$$$City:$Bl*****$$$$$Zip:$4***1$$$$$County:$M*****$$$$$
Employer_Present_Information:$$$$$$$DayPhone:$81*-***-**75(could be entered differently, form
 doesn't have a mask)$$$$$EvePhone:$$$$$$$Email:$de******@gmail.com$$$$$BirthDay:$01/21/****$$$$$Gender:$$$$$$$EmergName:$$$$$$$EmergRelation:$$$$$$$EmerDayPhone:$$$$$$$
EmerEvePhone:$$$$$$$SPNeeds:$$$$$$$EmrgInfo:$$$$$$$HowHeard:$$$$$$$CrseNo1:$$$$$$$
CrseTitle1:$$$$$$$DayTime1:$$$$$$$Cost1:$$$$$$$CrseNo2:$$$$$$$CrseTitle2:$$$$$$$
DayTime2:$$$$$$$Cost2:$$$$$$$CrseNo3:$$$$$$$CrseTitle3:$$$$$$$DayTime3:$$$$$$$
Cost3:$$$$$$$TotalCost:$$$$$$$Comments:

With some code -not yet tested for all values ---
I get the follwoing from the test record

done populating the array of fields within Contents
LastName:W*****
FirstName:D****MI:
State:CA
Street:63**** ***Ct
City:Bl*****
Zip:4***1
County:M*****
Employer_Present_Information:
DayPhone:81*-***-**75 (could be entered differently, form doesn't have a mask)
EvePhone:
Email:de******@gmail.com
BirthDay:01/21/****
Gender:
EmergName:
EmergRelation:
EmerDayPhone:
EmerEvePhone:
SPNeeds:
EmrgInfo:
HowHeard:
CrseNo1:
CrseTitle1:
DayTime1:
Cost1:
CrseNo2:
CrseTitle2:
DayTime2:
Cost2:
CrseNo3:
CrseTitle3:
DayTime3:
Cost3:

I have some issues with array index as I get near the end (logic issue).
But I do notice some change in format at MI: middle initial???

[orange]

I have mocked up your general situation. First I parsed your test record to get a list of the fieldnames with Contents.
I included the ":" with the field name to allow further processing.
I put them in a table "Smithfields" as below:

ID
Field1

1
LastName:

2
FirstName:

3
MI:

4
State:

5
Street:

6
City:

7
Zip:

8
County:

9
Employer_Present_Information:

10
DayPhone:

11
EvePhone:

12
Email:

13
BirthDay:

14
Gender:

15
EmergName:

16
EmergRelation:

17
EmerDayPhone:

18
EmerEvePhone:

19
SPNeeds:

20
EmrgInfo:

21
HowHeard:

22
CrseNo1:

23
CrseTitle1:

24
DayTime1:

25
Cost1:

26
CrseNo2:

27
CrseTitle2:

28
DayTime2:

29
Cost2:

30
CrseNo3:

31
CrseTitle3:

32
DayTime3:

33
Cost3:

34
TotalCost:

35
Comments:

Then I added another record to the "data" and called the table "csmith"

MyID        Auto
Contents  Memo

Then played with some vba to
1 --read the Smithfields data into an array
2 --open the Csmith table and process each record, by
3 --parsing the contents, looking for the fieldname and
4--displaying in immediate window the fieldname and value to be inserted into a table

Here is the vba I used

'---------------------------------------------------------------------------------------
' Procedure : getContents
' Author    : mellon
' Date      : 03-Mar-2017
' Purpose   : This is in responseto post
'             https://www.accessforums.net/showthread.php?t=64733&p=349386#post349386
'
'OP (csmith) has outlook data in Access memo field and needs to parse data
' this uses tables csmith ------- the raw data memo field is Contents
'                  smithFields ---the fields in sequence to be found in Contents
'---------------------------------------------------------------------------------------
'
Sub getContents()
    Dim Fldsarr(35) As String  'an array  of field names within each Contents field
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim s As String
    Dim str As String
    Dim strWork As String
    Dim x As Variant
    Dim i As Integer
10  On Error GoTo getContents_Error

20  Set db = CurrentDb
30  Set rs = db.OpenRecordset("smithFields")

    'routine to put expected field names into an array for processing

40  Do While Not rs.EOF
50      Fldsarr(rs!id) = rs!field1
        'Debug.Print Fldsarr(rs!id)
60      rs.MoveNext
70  Loop

    'array has been populated

80  rs.Close
90  Set rs = db.OpenRecordset("csmith")   'this is sample records from csmith's post
    ' made up a second record with mst fields populated

    'routine to parse Contents looking for Fieldnames

100 Do While Not rs.EOF
110     str = Replace(rs!Contents, vbCrLf, "")
120     Debug.Print "---------------New record -----------" & vbCrLf
130     For i = 1 To UBound(Fldsarr)

            'this logic needed to handle the last field and value in Contents

140         If i < UBound(Fldsarr) Then
                'get the start of next name
150             strWork = Mid(str, InStr(str, Fldsarr(i)))
160             s = Left(strWork, InStr(strWork, Fldsarr(i + 1)) - 1)
                'Debug.Print s   'for debugging
170             x = Split(s, ":")
180             Debug.Print "insert this value '" & x(1) & "' into the column(" & x(0) & ")"
190         Else
                'process the last field
200             strWork = Mid(str, InStr(str, Fldsarr(i)))
210             s = strWork
220             x = Split(s, ":")
230             Debug.Print "insert this value '" & x(1) & "' into the column(" & x(0) & ")"

240         End If
250     Next i
260     rs.MoveNext
270 Loop

280 On Error GoTo 0
290 Exit Sub

getContents_Error:

300 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure getContents of Module AWF_Related"

End Sub

Here is a listing of the output from the test records:

---------------New record -----------

insert this value 'W*****' into the column(LastName)
insert this value 'D****' into the column(FirstName)
insert this value '' into the column(MI)
insert this value 'CA' into the column(State)
insert this value '63**** ***Ct' into the column(Street)
insert this value 'Bl*****' into the column(City)
insert this value '4***1' into the column(Zip)
insert this value 'M*****' into the column(County)
insert this value '' into the column(Employer_Present_Information)
insert this value '81*-***-**75 (could be entered differently, form doesn't have a mask)' into the column(DayPhone)
insert this value '' into the column(EvePhone)
insert this value 'de******@gmail.com' into the column(Email)
insert this value '01/21/****' into the column(BirthDay)
insert this value '' into the column(Gender)
insert this value '' into the column(EmergName)
insert this value '' into the column(EmergRelation)
insert this value '' into the column(EmerDayPhone)
insert this value '' into the column(EmerEvePhone)
insert this value '' into the column(SPNeeds)
insert this value '' into the column(EmrgInfo)
insert this value '' into the column(HowHeard)
insert this value '' into the column(CrseNo1)
insert this value '' into the column(CrseTitle1)
insert this value '' into the column(DayTime1)
insert this value '' into the column(Cost1)
insert this value '' into the column(CrseNo2)
insert this value '' into the column(CrseTitle2)
insert this value '' into the column(DayTime2)
insert this value '' into the column(Cost2)
insert this value '' into the column(CrseNo3)
insert this value '' into the column(CrseTitle3)
insert this value '' into the column(DayTime3)
insert this value '' into the column(Cost3)
insert this value '' into the column(TotalCost)
insert this value '' into the column(Comments)
---------------New record -----------

insert this value 'Williams-Jonstone' into the column(LastName)
insert this value 'Jerimiah' into the column(FirstName)
insert this value 'P' into the column(MI)
insert this value 'WV' into the column(State)
insert this value '278 Greenapple Way' into the column(Street)
insert this value 'Wheeling' into the column(City)
insert this value '42341' into the column(Zip)
insert this value 'Obama' into the column(County)
insert this value 'TJ Maxx Ltd' into the column(Employer_Present_Information)
insert this value '2459872345' into the column(DayPhone)
insert this value '2459872346' into the column(EvePhone)
insert this value 'nonexistent@mymail.net' into the column(Email)
insert this value '24 January 1958' into the column(BirthDay)
insert this value 'M' into the column(Gender)
insert this value 'Mrs. Gladys Knight' into the column(EmergName)
insert this value 'Mother' into the column(EmergRelation)
insert this value '1800CallHome' into the column(EmerDayPhone)
insert this value '1800CallHome' into the column(EmerEvePhone)
insert this value 'Peanut allergyLactose intolerant' into the column(SPNeeds)
insert this value 'Hates Peanuts' into the column(EmrgInfo)
insert this value 'Television' into the column(HowHeard)
insert this value 'Myth101' into the column(CrseNo1)
insert this value 'Ancient Greek Mythology' into the column(CrseTitle1)
insert this value 'Tues 7-8AM' into the column(DayTime1)
insert this value '$400' into the column(Cost1)
insert this value '007' into the column(CrseNo2)
insert this value 'Mental Anguish' into the column(CrseTitle2)
insert this value 'Mon 9-10 AM' into the column(DayTime2)
insert this value '0' into the column(Cost2)
insert this value '2345' into the column(CrseNo3)
insert this value 'Intro to Arithmetic' into the column(CrseTitle3)
insert this value 'Wed 6-8 PM' into the column(DayTime3)
insert this value '23.68' into the column(Cost3)
insert this value '423.68' into the column(TotalCost)
insert this value 'This is a meaningless, nonsensical bit of freeform text to fill and test this long text(memo field) to show something in this option.' into the column(Comments)




I know others have answered, but thought another approach may be helpful.

Good luck with your project.