Senniger1
asked on
MS Access Search and Replace Using VBA
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
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
ASKER
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","R EQUEST")
Update DEADLINES set ACTIONDUE = replace(ACTIONDUE,"REISSUE DL","REISSUE DECLARATION")
If I only paste one line at a time they work individually.
Update DEADLINES set ACTIONDUE = replace(ACTIONDUE,"REQ","R
Update DEADLINES set ACTIONDUE = replace(ACTIONDUE,"REISSUE
If I only paste one line at a time they work individually.
If I only paste one line at a time they work individually.
Yes. Access SQL can only handle one update command per query.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
REQ
REQUEST
Then running the replace command against those to records would return
REQUEST
REQUESTUEST
ASKER
This was the best suggestions, however, I decided to go another route.
Update DEADLINES set ACTIONDUE = replace(ACTIONDUE,"REQ","R
Update DEADLINES set ACTIONDUE = replace(ACTIONDUE,"REISSUE
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.