Find and replace text in part of field

[lefty38]

I am looking for some code for a macro
that will remove the "RC" off of the Course code

example data
11112345RC
12121231
sdswe2323RC

Desired results
11112345
12121231
sdswe2323


more info
Find What: tblEnrollments.[Course Code] = "RC"
Replace With : tblEnrollments.[Course Code] = ""
Look in : tblEnrollments.[Course Code]
Match: Anypart of field
Search All

 

[boblarson]

Just use Replace in the query:

Replace([FieldNameHere], "RC", "")

 

[lefty38]

thank you Bob

it works as a query -
but when i call it from a macro using the RunSQL Command
on the macro action line

SQL Statement:
Replace(tblEnrollments![Course Code],"RC","") AS Expr1
FROM tblEnrollments;

Use transaction = Yes


it errors:
A runSQL action requires an argument consisting of an SQL statement.
for Example, an action query that appends records starts with INSERT INTO. A data-definition query that creates a table starts with create table.

I am not sure how else to call a quey inside of a macro other than use
runsql

 

[boblarson]

Why are you using it from a macro instead of a query?

 

[lefty38]

I have a macro that empties a table and imports new data from a large excel file
then (hopefully) removes the "RC" from the end of the [tblEnrollments]![Course Code]
So I can join [tblEnrollments]![Course Code] with other tables for other queries

 

[boblarson]

My suggestion - is just create an update query you can call once the import has been completed and then call it from the macro.