Replace using table lookup and not nested replace functions

[bbaccess]

Using Access database:

I want to shorten a string of text into abbreviations. I have a table with all long text and associated abbreviations and am having trouble with how to implement it.

Example:
"take one tablet by mouth three times daily" should read "take one tab PO TID" using a table that associates
"tablet" and "tab",
"by mouth" and "PO",
"three times daily" and "TID"

I'd like the table to be interactive, so I can add and delete abbreviations without having to dig through a nested replace function to make changes.

[ADezii]

I don't have access to Access (pun intended), so I'll simulate, rather crudly, how this can be done in Excel:

three times daily	TID
tablet	           tab
by mouth	         PO

Dim rng As Excel.Range
Dim rngRef As Excel.Range
Dim strString As String

strString = "take one tablet by mouth three times daily"

Set rng = Range("A1:A3")

For Each rngRef In rng
  If InStr(strString, rngRef) > 0 Then
    strString = Replace(strString, rngRef, rngRef.Offset(0, 1))
  End If
Next

Debug.Print "OUTPUT: " & strString
OUTPUT: take one tab PO TID

[ADezii]

Back with an Access Version.

1. Create a Table that will contain the String Expressions along with their abbreviations. Let's call this Table tblRefs, and it would look as follows:
   

   RefID	Expression			           Replace_With
   27	   tablet	                           tab
   28	   by mouth				             PO
   29	   three times daily			        TID
   30	   over the counter			         OTC
   31	   Non-Steroidal Inflammatories	     NSAIDS

2. Create a Table that would contain the actual String Expressions to be evaluated, we'll call it tblData:
   

   SID    MyStrings
   11	 take one tablet by mouth three times daily
   12	 I am prescribed over the counter medications on a daily basis
   13	 Non-Steroidal Inflammatories are great for swelling

3. Create a Query that contains a Calculated Field that will pass the String to be evaluated to a Public Function that will apply the abbreviations for every Record where applicable:
   

   SELECT tblData.MyStrings, fFormatString([MyStrings]) AS Return_String FROM tblData;

4. Function Definition:
   

   Public Function fFormatString(strTheString As String) As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strReplace As String
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("tblRefs", dbOpenForwardOnly)
   
   With rst
     Do While Not .EOF
       If InStr(strTheString, ![Expression]) > 0 Then
         strTheString = Replace(strTheString, ![Expression], ![Replace_With], , , vbTextCompare)
       End If
       .MoveNext
     Loop
       fFormatString = strTheString
   End With
   
   rst.Close
   Set rst = Nothing
   Set MyDB = Nothing
   End Function

5. Sample OUTPUT (layered):
   

   MyStrings							 
   Return_String
   --------------------------------------------------------------
   take one tablet by mouth three times daily			 
   take one tab PO TID
   --------------------------------------------------------------
   I am prescribed over the counter medications on a daily basis
   I am prescribed OTC medications on a daily basis
   --------------------------------------------------------------
   Non-Steroidal Inflammatories are great for swelling		 
   NSAIDS are great for swelling
   --------------------------------------------------------------

6. Hope this helps.

[twinnyfo]

Friends,

This is in no ways a critique of ADezii's code, as it should work fine. As a clarification (and as a caution) for working with recordsets, I always recommend the following (replacing lines 9-14 in 4 above):

With rst
    If Not (.BOF And .EOF) Then
        Call .MoveFirst
        Do While Not .EOF
            If InStr(strTheString, ![Expression]) > 0 Then
                strTheString = _
                    Replace( _
                        Expresssion:=strTheString, _
                        Find:=![Expression], _
                        Replace:=![Replace_With], _
                        Compare:=vbTextCompare)
            End If
            .MoveNext
        Loop
        fFormatString = strTheString
    End If
End With

Whenever we open a recordset, we want to make sure that there are records within that recordset before we try doing anything with that recordset. It is also wise to explicitly move to the first record (by default, DAO should do this--but it is not 100% faultless). Also, I added explicit references in the Replace() function. This is just a good habit to get into, so that you can see what variables and values are going into which argument. Yes, this does add a few keystrokes to your code building, but it is a good practice that can prevent headaches in the future and aid in troubleshooting .

Hope this hepps!

[twinnyfo]

BTW, ADezii's approach is identical to how I would approach it. The only concern is making sure that there are no abbreviations that might somehow get re-abbreviated. Based upon the list, there are none, but care should be taken that abbreviations don't overlap in any way.

My primary concern has to do with the source of the original text. Is this getting typed in by a user? Particularly with longer strings that may be abbreviated, this process could be asking for mistakes to happen. If a user types in everything correctly, but adds a comma or a period where it should not be, or mistypes a word, this code will not work at all.

You may want to address how the original (or even the final) string are built. That is for another thread, of course, but worthy of your consideration.

[ADezii]

This is in no ways a critique of ADezii's code,

Never will take it that way, I am always open to constructive criticism.

Whenever we open a recordset, we want to make sure that there are records within that recordset before we try doing anything with that recordset.

I could not agree with you more. Just out of curiosity, doesn't Do While Not .EOF cover that contingency? If there are no Records in the Recordset, then BOF and EOF will evaluate to True.

I added explicit references in the Replace() function. This is just a good habit to get into,

You are 100% correct in that it is a great habit to get into, unless, of course, you are running out the door (LOL).

P.S. - As you have already stated, there are a multitude of ways that this can go wrong and you could not possible count for every one of them. I feel as though a very interesting, and possibly practical use of this Code would be to create your own, custom AutoCorrect Options. With some slight modification and the use of the Change() Event, it shouldn't be difficult to accomplish. Always a pleasure to converse with you.

[twinnyfo]

ADezii:

doesn't Do While Not .EOF cover that contingency?

Technically speaking, yes it does. Again, I'm talking about good habits to get into. Here are my steps, in a nutshell:

1. Open the recordset
2. Check to see if the set is empty
3. If there are records, move to the first record
4. Loop through records --OR--
5. Do whatever you need to do with the recordset

Notice that Item 3 would fail if there are no records--which, again, if you were looping through records, although the Do While Not .EOF would, in fact, work, it does not guarantee that we are beginning from the first record.

Again, when working with Recordsets (which can be tricky enough), we always want to eliminate as many possible errors before they happen, rather than just trapping them through error handling--which, in my opinion, is not managing errors at all, it is identifying them.

Hope this hepps!

[twinnyfo]

@ADezii
And....... Keep up the great work!