By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

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

P: 33
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".

Expand|Select|Wrap|Line Numbers
  1. Dim strAllMeds As String
  2. Dim strSearch As String
  3.   strAllMeds = Me.TxtMedications
  4.  
  5. strSearch = Mid(strAllMeds, InStr(1, strAllMeds, "<div>", vbTextCompare) + 5, InStr(1, strAllMeds, "</div>", vbTextCompare) - 6)
  6. If InStr(1, strSearch, "PRN", vbTextCompare) > 0 Then
  7.   'Add to TxtPRNMeds
  8. Else
  9.   'Add to TxtScheduledMeds
  10. 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
Mar 23 '19 #1

✓ answered by JenniferM

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

Expand|Select|Wrap|Line Numbers
  1. If Len(Nz(Me.TxtMedications, "")) > 0 Then
  2.   GoTo SplitValues
  3.   Else
  4.   Exit Sub
  5.   End If
  6.  
  7. SplitValues:
  8. Dim strArray() As String
  9. Dim PRNBuild As String
  10. Dim ScheduledBuild As String
  11. Dim var As Variant
  12.  
  13.  strArray() = Split(Me.TxtMedications, "</div>")
  14.  
  15.  For var = LBound(strArray()) To UBound(strArray())
  16.     If InStr(1, strArray(var), "PRN", vbTextCompare) > 0 Then
  17.      PRNBuild = PRNBuild & strArray(var) & "<div> "
  18.      Else
  19.      ScheduledBuild = ScheduledBuild & strArray(var) & "<div>"
  20.      End If
  21.  Next var
  22.  
  23.  
  24. If Len(ScheduledBuild) > 6 Then
  25.   TxtScheduledMeds = Left(ScheduledBuild, Len(ScheduledBuild) - 6)
  26.   Else
  27.   TxtScheduledMeds = ScheduledBuild
  28.   End If
  29.  
  30. If Len(PRNBuild) > 6 Then
  31.   TxtPRNMeds = Left(PRNBuild, Len(PRNBuild) - 6)
  32.   Else
  33.   TxtPRNMeds = PRNBuild
  34.   End If

Thanks for the help, Nauticalgent!!

Share this Question
Share on Google+
10 Replies


Nauticalgent
P: 92
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?
Mar 23 '19 #2

Nauticalgent
P: 92
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim strArray() As String
  3.  
  4.     strArray() = Split(Me.TextField, "</div>")
  5.  
  6.     'Debug.Print strArray(0), strArray(1)
  7.     ' This would result in:
  8.     ' <div>Lacosamide 200 mg bid 
  9.     ' <div>Divalproex ER 1500 mg qhs
  10. 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.
Mar 23 '19 #3

P: 33
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.

Expand|Select|Wrap|Line Numbers
  1. If Len(Nz(Me.TxtMedications, "")) > 0 Then
  2.   GoTo SplitValues
  3.   Else
  4.   Exit Sub
  5.   End If
  6.  
  7. SplitValues:
  8. Dim strArray() As String
  9.  strArray() = Split(Me.TxtMedications, "</div>")
  10.  
  11.  Dim PRNBuild As String
  12.  Dim ScheduledBuild As String
  13.  
  14.  Dim element As Variant
  15.  For Each element In strArray
  16.    If InStr(1, element, "PRN", vbTextCompare) > 0 Then
  17.      PRNBuild = PRNBuild & element & "</div>"
  18.      End If
  19.    If InStr(1, element, "PRN", vbTextCompare) = 0 Then
  20.      ScheduledBuild = ScheduledBuild & element & "</div>"
  21.     End If
  22.  Next element
  23.  
  24. If Len(ScheduledBuild) > 6 Then
  25.   TxtScheduledMeds = Left(ScheduledBuild, Len(ScheduledBuild) - 6)  'Trim off extra "</div> string
  26.   Else
  27.   TxtScheduledMeds = ScheduledBuild
  28.   End If
  29.  
  30. If Len(PRNBuild) > 6 Then
  31.   TxtPRNMeds = Left(PRNBuild, Len(PRNBuild) - 6)  'Trim off extra "</div> string
  32.   Else
  33.   TxtPRNMeds = PRNBuild
  34.   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?
Mar 24 '19 #4

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

Expand|Select|Wrap|Line Numbers
  1. If Len(Nz(Me.TxtMedications, "")) > 0 Then
  2.   GoTo SplitValues
  3.   Else
  4.   Exit Sub
  5.   End If
  6.  
  7. SplitValues:
  8. Dim strArray() As String
  9. Dim PRNBuild As String
  10. Dim ScheduledBuild As String
  11. Dim var As Variant
  12.  
  13.  strArray() = Split(Me.TxtMedications, "</div>")
  14.  
  15.  For var = LBound(strArray()) To UBound(strArray())
  16.     If InStr(1, strArray(var), "PRN", vbTextCompare) > 0 Then
  17.      PRNBuild = PRNBuild & strArray(var) & "<div> "
  18.      Else
  19.      ScheduledBuild = ScheduledBuild & strArray(var) & "<div>"
  20.      End If
  21.  Next var
  22.  
  23.  
  24. If Len(ScheduledBuild) > 6 Then
  25.   TxtScheduledMeds = Left(ScheduledBuild, Len(ScheduledBuild) - 6)
  26.   Else
  27.   TxtScheduledMeds = ScheduledBuild
  28.   End If
  29.  
  30. If Len(PRNBuild) > 6 Then
  31.   TxtPRNMeds = Left(PRNBuild, Len(PRNBuild) - 6)
  32.   Else
  33.   TxtPRNMeds = PRNBuild
  34.   End If

Thanks for the help, Nauticalgent!!
Mar 24 '19 #5

Nauticalgent
P: 92
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.
Mar 24 '19 #6

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Mar 25 '19 #7

Nauticalgent
P: 92
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?
Mar 25 '19 #8

twinnyfo
Expert Mod 2.5K+
P: 3,284
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
Mar 25 '19 #9

Nauticalgent
P: 92
[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!
Mar 25 '19 #10

twinnyfo
Expert Mod 2.5K+
P: 3,284
NauticalGent:
Wow...that is a LOT of work!
Not really.....

;-)
Mar 25 '19 #11

Post your reply

Sign in to post your reply or Sign up for a free account.