asked on

Delete Text of a record under a field in an Access 2016 Query (not update query) and I need to replace Some Text Completely.

I am running a query on a table and the query is all set with the exception of I need to change some text. KEEP in mind I am a newbie with ACCESS and I am making all changes from the "Design View" on a query. So if you are not using this route it will not help me that much right now.

Question 1:
I have a field name "Categories"
I have multiple records like below under that field like below:

SDBS SES
AS400
Exchange
SDBS Link
SDBS Microsoft

I need to get rid of "SDBS" but then keep the rest of the string. So "SDBS SES" would only become "SES"

This has to be in a query and not an update for my purposes.

Question 2:

I need to completely change the text of a record to something entirely different.

For Example I need "SDBS Link" to be change to "Messenger"

Rey Obrero (Capricorn1)

for your question 1

place this in column in an UPDATE query

field Categories
table
Update to Trim(Replace([Categories], "SDBS",""))

rnhturner1

ASKER

My original subject line asked about whether this could be done outside of a "update query"

I don't want to change my original table, but change it in a query I am running for another group.

Rey Obrero (Capricorn1)

what exactly do you want to happen?

rnhturner1

ASKER

Here is what is happening. I have a user that connects to a query via multiple excel workbooks. We want to change some of the text of the records in that query but I don't want to change it for my main table and I don't want to change it for other queries other people are using.

So I really just need to take a basic query and change the text in that one Category Column so it only affects the query he uses to connect to his excel file. I could probably do it in excel but there are 25 or so pivot tables connected to this information and it would take some time to fix it all in excel.

Rey Obrero (Capricorn1)

using a select query

select Trim(Replace([Categories], "SDBS","")) As Category
from tableName

is this what you want?

rnhturner1

ASKER

This looks like it might work, but where do I put it in the select query?

Rey Obrero (Capricorn1)

in the design view of a new query

Field: Category:Trim(Replace([Categories], "SDBS",""))
Table: tableName

rnhturner1

ASKER

Getting Syntax error

Syntax error (comma) in query expression 'ManageEngineData'.[Trim(Replace([Categories],"SDBS",""))]'.

I copied your info word for word and my table name is "ManageEngineData" and this was selected in table field.

aikimark

Please test this

Select Replace([Categories], "SDBS Link", "Messenger") As CleanedCategories
From MyTable
Where [Categories] = "SDBS Link"
Union All
Select Replace([Categories], "SDBS ", "") As CleanedCategories
From MyTable
Where [Categories] <> "SDBS Link"

Open in new window

Rey Obrero (Capricorn1)

this in the dseign view of the query in the QBE (query builder editor)

Field: Category:Trim(Replace([Categories], "SDBS",""))
Table: ManageEngineData

this one is the SQL view of the query, you can copy this and paste directly to the SQL view of a NEW query

select Trim(Replace([Categories], "SDBS","")) As Category
from ManageEngineData

aikimark

If you have a lot of such substitutions, you should create a two-column table with the From and To values for your Categories data.

rnhturner1

ASKER

Rey - This worked great with a little experiment on my part. I could not get it to work in an existing query but it worked great in a new one. I then copied and pasted that Design View Column and pasted it into my older query and it did the trick. Thanks for sticking it through with me on this one.

Now for part 2:

In that same field is there a way to change the Word "LINK" (SINCE NOW IT ONLY SAYS LINK, and NOT SDBS LINK) to "Messenger" while keeping the same code to remove the SDBS?

rnhturner1

ASKER

aikimark - I would but that would mess up all the other user has in his connected Excel table. The columns need to stay as is with no other changes for it to work the way I need it to work. Rey has gotten me almost all the way there. Thanks for your help as well.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

If you create a new query that renames the Categories column, you can point all other queries to that query, instead of the base table. That way, your transforming query can change the column back to Categories.

rnhturner1

ASKER

Rey was patient and really good

aikimark

@rnhturner1

Since Rey's solution renames the column, why is this more acceptable than my solution?