Display only part of text in a field

[ruthie2012]

Access 2000. My database has fields containing text like:

<SELECT NAME="NAME2"><OPTION>Blue</OPTION><OPTION>Green</OPTION><OPTION>Red</OPTION></SELECT>

I would like to be able to remove the html from the text and show it on a report. e.g. the above as

Blue, Green, Red

Is this possible?

 

[antomack]

The following function should help in seperating out the relevant components of the string although it will take a bit of coding to loop through the string until all values are found. You will also need to use a Replace function on the passed data to change any double quotes to single so the function will run correctly.

Public Function StringBetween(strOrig As String, strStart As String, strEnd As String)
On Error GoTo Err_StringBetween
' Extracts the string that occurs between the start and end search strings specified

  Dim intStartPos As Integer, intEndPos As Integer
  Dim strProc As String
  
  ' find position of first occurence of strStart
  intStartPos = InStr(1, strOrig, strStart)
  If intStartPos > 0 Then
    intStartPos = intStartPos + Len(strStart)
  Else
    StringBetween = ""
    GoTo Exit_StringBetween
  End If
  
  ' find position of first occurence of strEnd after strStart
  intEndPos = InStr(intStartPos, strOrig, strEnd)
  
  If intEndPos = 0 Then
    StringBetween = ""
    GoTo Exit_StringBetween
  End If
  
  strProc = Mid(strOrig, intStartPos, intEndPos - intStartPos)
  
  StringBetween = strProc

Exit_StringBetween:
  Exit Function

Err_StringBetween:
  MsgBox Err.Description & Err
  Resume Exit_StringBetween

End Function

Example
StringBetween("<SELECT NAME='NAME2'><OPTION>Blue</OPTION><OPTION>Green</OPTION><OPTION>Red</OPTION></SELECT>", "<OPTION>", "</OPT") = Blue

Once you have the first value you will need to write code to loop through the string to check for any subsequent values, if no match is found then StringBetween returns an empty string
To find the second and subsequent values you need to search for the previous value followed by the string "</OPTION><OPTION>" as in
StringBetween("<SELECT NAME='NAME2'><OPTION>Blue</OPTION><OPTION>Green</OPTION><OPTION>Red</OPTION></SELECT>", "Blue</OPTION><OPTION>", "</OPT") = Green