?
Solved

Search and replace function query in MicroSoft Access

Posted on 2010-01-12
4
Medium Priority
?
328 Views
Last Modified: 2013-11-29
I have two tables one table is called IC which has only two fields:  Item# and Item_Description The other table which is a cross reference table called Abbreviation the Abbreviation table has two fields: Terms and Abbreviation.   I need to search for the Terms from the Abbreviation table in the Item_Description field and replace the term in the Item_Description field with the abbreviation that corresponds with the Term.

For expample,  if the Item_Description is 5XLarge Tooth Picks and there is a Term in the Abbreviation tables called 5XLarge with a abbreviation of 5XL the Item_Description should change to 5XL Tooth Picks.  The term could be anywhere in the Item_Description field.  For example, the Item_Description could be 3 of 5XLarge Tooth Picks which should change to 3 of 5XL Tooth Picks.  


0
Comment
Question by:trosss282003
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26297376
you can achieve this in VBA using two recordsets. is this an option?
0
 

Author Comment

by:trosss282003
ID: 26297518
capricorn1.  I know VBA to certain extent. Would you create a Loop with one recordset into the other recordset?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 26298249

dim rsIC as dao.recordset, rsAbb as dao.recordset
set rsIC=currentdb.openrecordset("IC")
set rsAbb=currentdb.openrecordset("Abbreviation")

rsIC.movefirst

do until rsIC.eof
      rsAbb.movefirst
      do until rsAbb.eof
            if instr(rsIC![Item_Description], rsAbb!Terms) then
                rsIC.edit
               rsIC![Item_Description]=replace(rsIC![Item_Description],rsAbb!Terms,rsAbb!Abbreviation)
                rsIC.update
            end if
      rsAbb.movenext
      loop
rsIC.movenext
loop

rsIC.close
rsAbb.close



back up your table first


0
 

Author Closing Comment

by:trosss282003
ID: 31676270
Works great.  Thank you very much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question