Extract Data Elements from Memo Field 2

[air1access]

I have memo filed in a table that I need to extract some data from and add as a new record to different table.

The memo field has the same structured value in it each time. Its the body of an Outlook email that is imported into my d/base.

The event file that you uploaded has been validated by the HPMS Marketing Module. Below are the details and status of the uploaded file:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Memo field contents:

File Upload ID: 35188
Contract Year: 2015
Event Period: September 2015
Event File Name: 09.08.2015 September V1 mce_marketing_events.xls
Date/Time of the file upload: 9/8/2015 10:38 AM
Status of the Upload: Successful
No. of Events successfully uploaded: 43

Note: Your marketing event file upload has passed its upload validation. All included events have been uploaded into the system
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I need to extract The string after "File Upload ID:", "Contract Year:", "Event Period:", etc...
All the way through "No. of Events successfully uploaded:"

These values are what I need appened as a new record in a different table...

any ideas/suggestions...?

 

[MajP]

You need to split the memo on the ":" using the split function. The only problem is there is a colon in the time value between hour and minutes. If not then it would be simpler because every odd item in the array would be the value. To show that, here is what happens if you split it.

File Upload ID -- index: 0
35188 -- index: 1
Contract Year -- index: 2
2015 -- index: 3
Event Period -- index: 4
September 2015 -- index: 5
Event File Name -- index: 6
09.08.2015 September V1 mce_marketing_events.xls -- index: 7
Date/Time of the file upload -- index: 8
9/8/2015 10 -- index: 9                    
38 AM -- index: 10
Status of the Upload -- index: 11
Successful -- index: 12
No. of Events successfully uploaded -- index: 13
43 -- index: 14

So item 1,3,5,7 are all good values
At 9 it messes up and splits 10:38
So you then have to account for that.

This function is passed in the memo field and then returns a collection of values. Each key of the collection is the label to the values left.

Public Function GetElements(strFileDetail) As Collection
  Dim elements() As String
  Dim i As Integer
  Dim theDatePart1 As String
  Dim theDatePart2 As String
  
  Set GetElements = New Collection
  strFileDetail = Replace(strFileDetail, vbCrLf, ":")
  elements = Split(strFileDetail, ":")
  For i = 0 To UBound(elements)
    Debug.Print Trim(elements(i)) & " -- index: " & i
    Select Case i
     Case 1, 3, 5, 7, 12, 14
       'The key is the label in the memo
       GetElements.Add Trim(elements(i)), elements(i - 1)
     Case 9
       theDatePart1 = Trim(elements(i))
     Case 10
       theDatePart2 = Trim(elements(i))
       GetElements.Add theDatePart1 & ":" & theDatePart2, elements(i - 1)
     End Select
  Next i
End Function

you can test that it works.

Public Sub TestElements()
  Dim memo As String
  Dim myElements As Collection
  Dim element As Variant
  memo = Forms!frmMemo.txtbxData
  Set myElements = GetElements(memo)
  'Verify that the collection contains just the values
  For Each element In myElements
    Debug.Print element
  Next element
  'Verify that the you can call the values by their key
  Debug.Print myElements("Contract Year")
End Sub

 

[air1access]

I'm still not getting it to work...

How do I get "element" added as a new record in a different table..?

I need to have the value after "Upload File ID" to be a value in a field.
Basically everything after the ":" with the 7 "elements" shown below will be a new record...

File Upload ID: 35188
Contract Year: 2015
Event Period: September 2015
Event File Name: 09.08.2015 September V1 mce_marketing_events.xls
Date/Time of the file upload: 9/8/2015 10:38 AM
Status of the Upload: Successful
No. of Events successfully uploaded: 43

 

[MajP]

The function returns a collection that has each element in it. That is the challenging part. From there you could either build another routine to do an insert query by passing it the collection. If you cannot figure it out, I can try to work it later.

 

[MajP]

complete code

Public Function GetElements(strFileDetail) As Collection
  Dim elements() As String
  Dim i As Integer
  Dim theDatePart1 As String
  Dim theDatePart2 As String
  
  Set GetElements = New Collection
  strFileDetail = Replace(strFileDetail, vbCrLf, ":")
  elements = Split(strFileDetail, ":")
  For i = 0 To UBound(elements)
    Debug.Print Trim(elements(i)) & " -- index: " & i
    Select Case i
     Case 1, 3, 5, 7, 12, 14
       'The key is the label in the memo
       GetElements.Add Trim(elements(i)), elements(i - 1)
     Case 9
       theDatePart1 = Trim(elements(i))
     Case 10
       theDatePart2 = Trim(elements(i))
       GetElements.Add theDatePart1 & ":" & theDatePart2, elements(i - 2)
     End Select
  Next i
End Function

Public Sub InsertElements()
  Dim memo As String
  Dim myElements As Collection
  Dim element As Variant
  Dim strSql As String
  Dim FileID As String
  Dim ContractYear As Integer
  Dim EventPeriod As String
  Dim FileName As String
  Dim UploadDate As String
  Dim status As String
  Dim EventsUploaded As Integer
  'your code here to get the memo field
  DoCmd.OpenForm ("frmMemo")
  memo = Forms!frmMemo.txtbxData

  'call function and get a collection of elements
  Set myElements = GetElements(memo)
  
  'text needs single quotes, dates need #
  FileID = myElements("File Upload ID")
  FileID = "'" & FileID & "'"
  ContractYear = myElements("Contract Year")
  EventPeriod = myElements("Event Period")
  EventPeriod = "'" & EventPeriod & "'"
  FileName = ("Event File Name")
  FileName = "'" & FileName & "'"
  UploadDate = myElements("Date/Time of the file upload")
  UploadDate = "#" & UploadDate & "#"
  status = myElements("Status of the Upload")
  status = "'" & status & "'"
  EventsUploaded = myElements("No. of Events successfully uploaded")

  strSql = "INSERT INTO NewTable (FileID, ContractYear, EventPeriod, FileName, UploadDate, Status, EventsUploaded)"
  strSql = strSql & " values (" & FileID & "," & ContractYear & "," & EventPeriod & "," & FileName & "," & UploadDate &   "," & status & "," & EventsUploaded & ")"
  Debug.print strSql
  currentdb.execute strSql
end sub

 

[air1access]

Thank you Majp... I got it to work just fine.
Awesome..!! Thank you so much..!!

air1access