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

Replace using table lookup and not nested replace functions

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

"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.
Sep 18 '20 #1
Share this Question
Share on Google+
7 Replies

Expert 5K+
P: 8,748
I don't have access to Access (pun intended), so I'll simulate, rather crudly, how this can be done in Excel:
Expand|Select|Wrap|Line Numbers
  1. three times daily    TID
  2. tablet               tab
  3. by mouth             PO
Expand|Select|Wrap|Line Numbers
  1. Dim rng As Excel.Range
  2. Dim rngRef As Excel.Range
  3. Dim strString As String
  5. strString = "take one tablet by mouth three times daily"
  7. Set rng = Range("A1:A3")
  9. For Each rngRef In rng
  10.   If InStr(strString, rngRef) > 0 Then
  11.     strString = Replace(strString, rngRef, rngRef.Offset(0, 1))
  12.   End If
  13. Next
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "OUTPUT: " & strString
  2. OUTPUT: take one tab PO TID
Sep 19 '20 #2

Expert 5K+
P: 8,748
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:
    Expand|Select|Wrap|Line Numbers
    1. RefID    Expression                       Replace_With
    2. 27       tablet                               tab
    3. 28       by mouth                             PO
    4. 29       three times daily                    TID
    5. 30       over the counter                     OTC
    6. 31       Non-Steroidal Inflammatories         NSAIDS
  2. Create a Table that would contain the actual String Expressions to be evaluated, we'll call it tblData:
    Expand|Select|Wrap|Line Numbers
    1. SID    MyStrings
    2. 11     take one tablet by mouth three times daily
    3. 12     I am prescribed over the counter medications on a daily basis
    4. 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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblData.MyStrings, fFormatString([MyStrings]) AS Return_String FROM tblData;
  4. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fFormatString(strTheString As String) As String
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim strReplace As String
    6. Set MyDB = CurrentDb
    7. Set rst = MyDB.OpenRecordset("tblRefs", dbOpenForwardOnly)
    9. With rst
    10.   Do While Not .EOF
    11.     If InStr(strTheString, ![Expression]) > 0 Then
    12.       strTheString = Replace(strTheString, ![Expression], ![Replace_With], , , vbTextCompare)
    13.     End If
    14.     .MoveNext
    15.   Loop
    16.     fFormatString = strTheString
    17. End With
    19. rst.Close
    20. Set rst = Nothing
    21. Set MyDB = Nothing
    22. End Function
  5. Sample OUTPUT (layered):
    Expand|Select|Wrap|Line Numbers
    1. MyStrings                             
    2. Return_String
    3. --------------------------------------------------------------
    4. take one tablet by mouth three times daily             
    5. take one tab PO TID
    6. --------------------------------------------------------------
    7. I am prescribed over the counter medications on a daily basis
    8. I am prescribed OTC medications on a daily basis
    9. --------------------------------------------------------------
    10. Non-Steroidal Inflammatories are great for swelling         
    11. NSAIDS are great for swelling
    12. --------------------------------------------------------------
  6. Hope this helps.
Sep 20 '20 #3

Expert Mod 2.5K+
P: 3,543

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):

Expand|Select|Wrap|Line Numbers
  1. With rst
  2.     If Not (.BOF And .EOF) Then
  3.         Call .MoveFirst
  4.         Do While Not .EOF
  5.             If InStr(strTheString, ![Expression]) > 0 Then
  6.                 strTheString = _
  7.                     Replace( _
  8.                         Expresssion:=strTheString, _
  9.                         Find:=![Expression], _
  10.                         Replace:=![Replace_With], _
  11.                         Compare:=vbTextCompare)
  12.             End If
  13.             .MoveNext
  14.         Loop
  15.         fFormatString = strTheString
  16.     End If
  17. 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!
Sep 21 '20 #4

Expert Mod 2.5K+
P: 3,543
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.
Sep 21 '20 #5

Expert 5K+
P: 8,748
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.
Sep 21 '20 #6

Expert Mod 2.5K+
P: 3,543
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!
Sep 21 '20 #7

Expert Mod 2.5K+
P: 3,543
And....... Keep up the great work!
Sep 21 '20 #8

Post your reply

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