Need to search each line in RTF field / return lines that contain specific string

[JenniferM]

Hello, esteemed experts!

I have a RTF field called "TxtMedications" that contains multiple lines of text, each of these obviously preceded by a <div> tag. For example:

Lacosamide 200 mg bid
Divalproex ER 1500 mg qhs
Pregabalin 50 mg q8h
Ibuprofen 400 mg tid PRN
Cetirizine 10 mg daily PRN

Or in string form...

<div>Lacosamide 200 mg bid</div>
<div>Divalproex ER 1500 mg qhs</div>
<div>Pregabalin 50 mg q8h</div>
<div>Ibuprofen 400 mg tid PRN</div>
<div>Cetirizine 10 mg daily PRN</div>

What I'm trying to do (and where I'm currently stuck) is separating out all lines which contain the string "PRN" into another RTF text box, called "TxtPRNMeds" and having the remainder put into a RTF text box called "TxtScheduledMeds".

I'm able to use the InStr() and Mid() functions to return the first string of text between the tags and search that string for "PRN".

Dim strAllMeds As String
Dim strSearch As String
  strAllMeds = Me.TxtMedications
 
strSearch = Mid(strAllMeds, InStr(1, strAllMeds, "<div>", vbTextCompare) + 5, InStr(1, strAllMeds, "</div>", vbTextCompare) - 6)
If InStr(1, strSearch, "PRN", vbTextCompare) > 0 Then
  'Add to TxtPRNMeds
Else
  'Add to TxtScheduledMeds
End If

So in the above, I'm able to isolate the string "Lacosamide 200 mg bid" and determine that it does not contain the matching string "PRN" but that's about as far as I've gotten to this point. Any advice on how to search the subsequent rich text lines?

Thanks for any help.

Signed,
Perplexed

[Nauticalgent]

Dear Perplexed,

Just so I understand correctly, you have a Memo field (Long Text) that is formatted as RTF on the bound form? Also, where are the <div> tags coming from, are they imported from an external source?

[Nauticalgent]

Hi Perplexed...

A couple of ways to tackle this. Having never used RTF on a table or field level, I had to educate myself on the HTML tags.

I did a quick mock-up of your form and table and in my opinion the best (easiest way) to go about this would be to use the Split() function to populate and array to hold the data:

Private Sub Form_Current()
    Dim strArray() As String
 
    strArray() = Split(Me.TextField, "</div>")
 
    'Debug.Print strArray(0), strArray(1)
    ' This would result in:
    ' <div>Lacosamide 200 mg bid 
    ' <div>Divalproex ER 1500 mg qhs
End Sub

Then you could use a For Each loop to examine each Array element for the "PRN" string and act accordingly.

My first inclination would have been to use Regular expressions, but in this case, that would be using a bazooka to kill a mosquito.

[JenniferM]

That's a clever way of doing it, and sounds like it'd be the most efficient way too. For clarification on your earlier post, I have a memo field formatted in rich text that needs to be split into two textboxes formatted with rich text (TxtScheduledMeds and TxtPRNMeds). For testing and whatnot, I'm using a rich text textbox, that I'm calling TxtMedications, which holds the string needing to be split.

I've implemented your technique and tried to loop through the array elements to find matching string.

If Len(Nz(Me.TxtMedications, "")) > 0 Then
  GoTo SplitValues
  Else
  Exit Sub
  End If
 
SplitValues:
Dim strArray() As String
 strArray() = Split(Me.TxtMedications, "</div>")
 
 Dim PRNBuild As String
 Dim ScheduledBuild As String
 
 Dim element As Variant
 For Each element In strArray
   If InStr(1, element, "PRN", vbTextCompare) > 0 Then
     PRNBuild = PRNBuild & element & "</div>"
     End If
   If InStr(1, element, "PRN", vbTextCompare) = 0 Then
     ScheduledBuild = ScheduledBuild & element & "</div>"
    End If
 Next element
 
If Len(ScheduledBuild) > 6 Then
  TxtScheduledMeds = Left(ScheduledBuild, Len(ScheduledBuild) - 6)  'Trim off extra "</div> string
  Else
  TxtScheduledMeds = ScheduledBuild
  End If
 
If Len(PRNBuild) > 6 Then
  TxtPRNMeds = Left(PRNBuild, Len(PRNBuild) - 6)  'Trim off extra "</div> string
  Else
  TxtPRNMeds = PRNBuild
  End If

The code is recognizing all the array variants, but now the problem I'm running into is that it's adding each element to the PRNbuild string (even when there is no match). I can't seem to pinpoint the reason for this.

So after I run the above code, I get:

TxtScheduledMeds: (Null)

TxtPRNMeds:
Lacosamide 200 mg bid
Divalproex ER 1500 mg qhs
Pregabalin 50 mg q8h
Ibuprofen 400 mg tid PRN
Cetirizine 10 mg daily PRN

When I'm hoping to get:

TxtScheduledMeds:
Lacosamide 200 mg bid
Divalproex ER 1500 mg qhs
Pregabalin 50 mg q8h

TxtPRNMeds:
Ibuprofen 400 mg tid PRN
Cetirizine 10 mg daily PRN

Can anybody see where I'm messing this up?

[JenniferM]

I played around with the loop code a little bit, and it looks like this solved the problem:

If Len(Nz(Me.TxtMedications, "")) > 0 Then
  GoTo SplitValues
  Else
  Exit Sub
  End If
 
SplitValues:
Dim strArray() As String
Dim PRNBuild As String
Dim ScheduledBuild As String
Dim var As Variant
 
 strArray() = Split(Me.TxtMedications, "</div>")
 
 For var = LBound(strArray()) To UBound(strArray())
    If InStr(1, strArray(var), "PRN", vbTextCompare) > 0 Then
     PRNBuild = PRNBuild & strArray(var) & "<div> "
     Else
     ScheduledBuild = ScheduledBuild & strArray(var) & "<div>"
     End If
 Next var
 
 
If Len(ScheduledBuild) > 6 Then
  TxtScheduledMeds = Left(ScheduledBuild, Len(ScheduledBuild) - 6)
  Else
  TxtScheduledMeds = ScheduledBuild
  End If
 
If Len(PRNBuild) > 6 Then
  TxtPRNMeds = Left(PRNBuild, Len(PRNBuild) - 6)
  Else
  TxtPRNMeds = PRNBuild
  End If

Thanks for the help, Nauticalgent!!

[Nauticalgent]

Well done JenniferM/Perplexed, glad I could help get you pointed in the right direction...

That being said, I have to ask: I am assuming the txtMedications field holds a list of medications for a patient.

If I have that correctly then your tables are not structured correctly. You should have table with a Primary Key (PK) for the patients and another table that hold the medications for each patient. The medication table would hava a Foreign Key (FK) that would correspond to that patient's PK - each record would hold one and only one prescription which would eliminate the need for this code altogether and you could do all of this with a simple query.

What you have now is the equivalent to a multi-value field which should be avoided...

Again, this all based on the accuracy of my assumption. I am shocked some of the other members have not descended upon you like a pack of wolves.

[twinnyfo]

NauticalGent:
I am shocked some of the other members have not descended upon you like a pack of wolves.

I was away for the weekend. Good catch (and advice), NauticalGent!

[Nauticalgent]

Speaking of Wolves...thanks TwinnyFo.

P.S. How do you quote somebody on this forum? I have not been able to figure it out. HTML tags?

[twinnyfo]

Left Square Bracket Quote Right Square Bracket Left Square Bracket Highlight Right Square Bracket NauticalGent: Left Square Bracket /Highlight Right Square Bracket Carriage Return Add all y'all's text Left Square Bracket /Quote Right Square Bracket

[Nauticalgent]

[TwinnyFo:]
Left Square Bracket Quote Right Square Bracket Left Square Bracket Highlight Right Square Bracket NauticalGent: Left Square Bracket /Highlight Right Square Bracket Carriage Return Add all y'all's text Left Square Bracket /Quote Right Square Bracket

Wow...that is a LOT of work!

[twinnyfo]

NauticalGent:
Wow...that is a LOT of work!

Not really.....

;-)