Values in a memo field

[sesling]

I have a table that contains a memo field. I would like to search the memo field and display values that exist between <recCnt> and </recCnt>. ex. ....<recCnt>37868368</recCnt)..... So in the example I want the query to return 37868368. The values will appear in different locations within the memo field. I pasted two sample entries from the memo field below.

Entry 1 <payload><recCnt>37868368</recCnt><physicalFileName>/work/share/local/apps/samplefile1.ABC</physicalFileName></payload>

Entry 2


<payload><pfId>cd57e241-d165-4632-9eef-d21990ac61d4</pfId><recCnt>37868368</recCnt><physicalFileName>/work/share/local/apps/samplefile2.ABC</physicalFileName></payload>

[pdebaets]

You can use the xg_GetWordsBetween function to find text between two other strings. The xg_GetWordsBetween function is in our string functions module available for download here: http://peterssoftware.com/strfn.htm

Ex.:

dim strResult as string
strResult = xg_GetWordsBetween(Forms!MyForm!MyMemoControlName, "<recCnt>","</recCnt>")

[sesling]

Thx for the info...However our company policy prohibits me from downloading the function. Would there be another way using the existing functions inside MS Access to get at the data?

[sesling]

I stumbled across a solution. Not sure its the best way but it worked.

Mid([payload],((InStr([payload],"<recCnt>"))+8),(InStr([payload],"</recCnt>"))-(InStr([payload],"<recCnt>"))-8)

payload = memo field name
recCnt = first parameter I was looking for
/recCnt = second parameter I was looking for

[pdebaets]

Here's the xg_GetWordsBetween function:

Function xg_GetWordsBetween(sMain As String, s1 As String, s2 As String) As String
'* Returns a trimmed substring of the string 'sMain' that lies between substrings s1 and s2
'* Ex.: xg_GetWordsBetween("The Lazy Fox", "The", "Fox") returns "Lazy".
Dim Rtn As String
Dim iStart As Integer
Dim iEnd As Integer
On Error Resume Next

iStart = InStr(1, sMain, s1)
If iStart = 0 Then
    Rtn = ""
Else
    iStart = iStart + Len(s1)
    iEnd = InStr(iStart, sMain, s2)
    Rtn = Trim(Mid(sMain, iStart, iEnd - iStart))
End If
xg_GetWordsBetween = Rtn

On Error GoTo 0
End Function