How to read a memo field in MS Access Database ?

Question

Tell me how to read a memo field using VB.NET. The contents are separated by a delimiter |. In a single memo field it contains many other values (numbers). and each number is separated by |.

I only required to pick one value (number) only from the memo field. 

eg

Field Name Field Type
Rate1 Memo

Rate1 1.22 | 1.23 | 1.32 | 1.44 | ........ and so on

Teach me how to read field value 1.44

thanks

10 points for the best answer

BlueFeather · Accepted Answer

Here is an example in VBA (tested in Access 2003):

Private Function Extract(N As Integer, MyString As String, delim As String) As String
'MyString contains the contents of the Memo field
'(delim is assumed to be a single character)
Dim k As Integer, p As Integer, q As Integer
Dim ss As String, rslt As String

Extract = "": rslt = ""
If N <= 0 Then Exit Function

ss = Trim(MyString)

'(ensure that the string ends with the delimiter)
If Right$(ss, 1) <> delim Then ss = ss & delim

k = 0: p = 0
For q = 1 To Len(ss)
  If Mid$(ss, q, 1) = "|" Then
    k = k + 1
    If k = N Then
      rslt = Trim(Mid$(ss, p + 1, q - p - 1))
      Exit For
    Else
      p = q
    End If
  End If
Next
Extract = rslt
End Function

To get the 4th value (1.44):
SomeString = Extract(4, MyString, "|")

(conversion to VB.NET is left for you to do)

§

? · Answer

Access Memo Field

How to read a memo field in MS Access Database ?

2 Answers