Solved

MS Access Search and Replace Using VBA

Posted on 2016-10-06
6
91 Views
Last Modified: 2016-10-06
I have a Microsoft Access 2010 database.

In it I have a table named DEADLINES.  In the table DEADLINES, I have a field named ACTIONDUE.

I want VBA (visual basic) code to go through the field ACTIONDUE and make the following changes.
   Search for the word "REQ" and replace it with REQUEST (Anywhere in the field)
   Search for the word "REISSUE DL" and replace it with "REISSUE DECLARATION" (Whole field)

I'd prefer to do this using VBA instead of in SQL because I have about 75 changes to make.

Can anyone help me?

Thanks in advance!

LDMueller
0
Comment
Question by:Senniger1
6 Comments
 
LVL 1

Expert Comment

by:Rajesh Joshi
ID: 41831948
Instead write a query like this
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REQ","REQUEST")
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REISSUE DL","REISSUE DECLARATION")

It will take same time as VBA.
you can use excel, to create 3 columns
1)Old String 2) New String 3) SQL statement formula from col. 1& 2.
then copy paste one by one from 3rd column.
0
 

Author Comment

by:Senniger1
ID: 41831970
I can be open to this, however, I pasted the following in my SQL window and ran it.  I get Syntax error (missing operator) in query expression.

Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REQ","REQUEST")
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REISSUE DL","REISSUE DECLARATION")

If I only paste one line at a time they work individually.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41832014
If I only paste one line at a time they work individually.

Yes. Access SQL can only handle one update command per query.

/gustav
0
Industry Leaders: 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!

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 41832025
your best bet would be to create a translation table (tblTranslate) with fields "ActionDue" (make this the PK in the table) and "ActionDueNew".  Fill in all of those values for the 70+ changes you need to make.  

Then create a single query to select the values from the original table an this new table, something like:

SELECT Deadlines.ActionDue, tblTranslate.ActionDueNew
FROM Deadlines 
INNER JOIN tblTranslate 
ON Deadlines.ActionDue = tblTranslate.ActionDue

Open in new window

Once you have that query, attempt to manually change one of the ActionDue values, to determine whether the query is updateable.  If it is, you should be able to simply modify the SELECT query to an UPDATE query and enter tblTranslate.ActionDueNew in the Update To cell of the query grid.

If, however, the SELECT query is not updateable, you could use VBA to create a recordset and loop through the recordset, something like:

Dim db as dao.database
Dim rs as dao.recordset
dim strsql as string

strsql = "SELECT ActionDue, ActionDueNew FROM tblTranslate"
set db = currentdb
set rs = db.openrecordset(strsql)
while not rs.eof
    strSQL = "UPDATE Deadline SET [ActionDue] = '" & rs!ActionDueNew & "' " _
                 & "WHERE [ActionDue] = '" & rs!ActionDue & "'"
    db.execute strsql, dbfailonerror
    rs.movenext
wend

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41832030
The problem with using the Replace() function in this context is that you could have a value where the value "REQ" shows up in more than one record type for that field.  So if you already have values for:

REQ
REQUEST

Then running the replace command against those to records would return
REQUEST
REQUESTUEST
1
 

Author Closing Comment

by:Senniger1
ID: 41832568
This was the best suggestions, however, I decided to go another route.
0

Featured Post

Industry Leaders: 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

680 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