Extract information from memo field

[Amy]

Hello everyone:

Happy New Year!

We have a memo field (on SQL Server back-end) that contains different field
information on separate lines. (We are eventually going to separate this
information into separate table fields.) What would be the easiest way to
separate the information into different fields using Microsoft Access
queries? The header and headings are always the same. Any help would be
appreciated. Thank you in advance.

Examples:

Repayment Details
Frequency: monthly
Installments: 12
Per installment: $100.00
Total: $1200.00
Start date: 30-Dec-06
End date: 30-Nov-07
There may be additional notes here.

Repayment Details
Frequency: monthly
Installments: 6
Per installment: $150.00
Total: $900.00
Start date: 01-Sept-06
End date: 01-Feb-07
There may be additional notes here.

Result:

Frequency Installments Per installment Total Start date End date
monthly 12 $100.00 $1200.00 30-Dec-06 30-Nov-07
monthly 6 $150.00 $900.00 01-Sept-06 01-Feb-07

 

[John Nurick]

Hi Amy,

Happy New Year to you!

Here's a vba function that can be called in a query. You pass it the
contents of the memo field, the field name, and whether you want the
first, second... occurrence of that name, and with a bit of luck it will
give you the corresonding value. Be sure to test it carefully on a copy
of your database!

Function GetValue369(S As Variant, FieldName As String, _
ItemNumber As Long) As Variant

Dim Line As Variant, Lines As Variant
Dim j As Long

GetValue369 = Null
If IsNull(S) Then Exit Function

Lines = Split(S, vbCrLf) 'split memo into array of lines
j = 0
For Each Line In Lines
If Line Like FieldName & ": *" Then 'item found
j = j + 1
If j = ItemNumber Then
GetValue369 = Trim(Mid(Line, Len(FieldName) + 2))
Exit For
End If
End If
Next

End Function

 

[John Nurick]

Hi Amy,

The problem is that # has a special meaning for the Like operator: it
signifies "any digit 0-9". If you want Like to look for an actual # you
need to enclose it in square brackets. Changing

If Line Like FieldName & ": *" Then 'item found

to

If Line Like Replace(FieldName, "#", "[#]") & ": " Then

should do the trick.

If you look up the Like operator in Help you'll see that there are other
characters that have special meanings: watch out for these as well.