Improve company productivity with a Business Account.Sign Up

x
?
Solved

MS Access Search and Replace Using VBA

Posted on 2016-10-06
6
Medium Priority
?
483 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 53

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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 50

Accepted Solution

by:
Dale Fye earned 2000 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 50

Expert Comment

by:Dale Fye
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
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…

606 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