Solved

MS Access Search and Replace Using VBA

Posted on 2016-10-06
6
104 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
[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
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 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