Extract Memo Data to Table 1

[DugyWugy]

Hello List,

I have been looking at the threads for a couple days now - trying to piece together some code to perform the following task: I have a memo field that contains some semi-normalized data, I would like to query this field and extract the required data into separate fields.
Example memo data:

Call Date: 02/03/2006 Time: 07:16:18 AM
Transmit Date: 06/27/2006 Time: 07:18:21 AM
Address: 201 , MAIN ST
Contact Name: ANY NAME

I've tried using the Mid function and using the sequence of the characters to query the data out - but there are discrepancies that won't allow me to do this.
Is there a way to query for "Call Date:" and then output the preceding characters in a table "02/03/2006" then carry on with the same process (loop through) the rest of the fields?
Any guidance will be appreciated.
Doug

 

[CautionMP]

DugyWugy,
Here is a sample routine that should get you moving in the right direction. It is written to handle your memo field in the format you supplied but it could be made "smart' to look for the actual field names ([tt]Call Date, Transmit Date, ...[/tt]).

It could be called from a query as is to return a component of the memo field as part of a query ([tt]Exp1: ParseData([YourMemoFieldName], "Call Date")[/tt] which retunrs [tt]2/3/2006 7:16:18 AM[/tt].

Or you could add it to a recordset loop to process all the records you have into all the fields in a singlr routine.

Function ParseData(MemoText As String, FieldName As String) As Variant
Dim intField As Integer
Dim strData() As String, strTemp As String

'*Convert the memo data to a delimited format
'Add delimiter for Time
strTemp = Replace(MemoText, "Time:", ":Time:")
'Add delimiter for line break
strTemp = Replace(strTemp, vbCrLf, ":")
'Move all the pieces and parts into seperate holders
strData = Split(strTemp, ":")
'Take the leading/trailing spaces out
For intField = 0 To UBound(strData)
  strData(intField) = Trim(strData(intField))
Next intField

'Now get the requested data field
Select Case FieldName
  Case "Call Date"
    ParseData = CDate(strData(1) & " " & strData(3) & ":" & strData(4) & ":" & strData(5))
  Case "Transmit Date"
    ParseData = CDate(strData(7) & " " & strData(9) & ":" & strData(10) & ":" & strData(11))
  Case "Address"
    ParseData = strData(13)
  Case "Contact Name"
    ParseData = strData(15)
  Case Else
    ParseData = "Uknown FieldName"
End Select
End Function

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

Another way is to play with the InStr, Mid and Trim functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DugyWugy]

Thanks CautionMP,

This great - I'll start working on this right away. I'll also review the information from PHV regarding InStr, Mid, and Trim. I know this kind of functionality will be invaluable in the future.

Regards,
DugyWugy