?
Solved

Search and replace function query in MicroSoft Access

Posted on 2010-01-12
4
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

762 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