Need to update data in a query for the output

[GMFTM]

I am using a query to pull data from a tbl. In the query I want to update the names in a specific field.

Example Card# 123 to Card and Phone with Cord to Phone

Couple of things I tried to no eval:

Dealer_Name: IIf([Dlr_Name]="Carmax #","CarMax",IIf([Dlr_Name]="DriveTime","DriveTime",[Dlr_Name]))

Dealer_Name: IIf([Dlr_Name] Like "Carmax #","CarMax",IIf([Dlr_Name] Like "DriveTime","DriveTime",[Dlr_Name]))

I am trying to keep from creating a tbl and then updating there, would like to do it all in the select query.

Any help is appreciated.

 

[Joe4]

Welcome to the Board!

Do you physically want to update the data in the underlying table?
A Select Query cannot update data, it just returns it.
If you wanted to update data via a query, you would use an Update Query.
See: https://support.office.com/en-us/ar...te-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514

 

[GMFTM]

Welcome to the Board!

Do you physically want to update the data in the underlying table?
A Select Query cannot update data, it just returns it.
If you wanted to update data via a query, you would use an Update Query.
See: https://support.office.com/en-us/ar...te-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514[/QUOTE


Yes sir, that is correct, I was just wondering if there was a way to yse the SQL side and update the data in the query for the output without having to create a tbl and then updata and return the data back as a query form. I was trying to cut out a few steps.

Regards,

 

[Joe4]

I was just wondering if there was a way to yse the SQL side and update the data in the query for the output without having to create a tbl and then updata and return the data back as a query form. I was trying to cut out a few steps.

I am afraid that statement isn't really clear, so let's try answering the following questions:

1. Is this data in SQL or Access?

2. Do you want to update the underlying Table data permanently, or are you just trying to change the value of the data in the query (for output purposes), but NOT permanently change the underlying data?

In any event, I don't think you need to create any new tables, unless you want to have a mapping table which shows the replacements you want to make (i.e. a two field table that shows the string you want to replace, and then string you want to replace it with).

 

[GMFTM]

I am afraid that statement isn't really clear, so let's try answering the following questions:

1. Is this data in SQL or Access?

2. Do you want to update the underlying Table data permanently, or are you just trying to change the value of the data in the query (for output purposes), but NOT permanently change the underlying data?

In any event, I don't think you need to create any new tables, unless you want to have a mapping table which shows the replacements you want to make (i.e. a two field table that shows the string you want to replace, and then string you want to replace it with).

It is in Access, I was referring to the SQL in Access, where you can writh the code/statements. The change is a permenant change, I am replacing one value for another.

Regards,

 

[Joe4]

Then refer back to the link I provided on Update Queries. Update Queries are what you use to permanently update data.
Note that a Query in Access is really just SQL code (or at least Access's version of SQL). You can see this easily by opening any Query in Access and switching to SQL View.

Basically, you just create a query in Access that identifies the records you want to update (via the Criteria), and then what you want to Update it to (using the "Update To" line).
I would recommend starting out doing just one replacement per query (i.e. replacing all instances of "Carmax #" with "CarMax").

If you only have a few, you can set up multiple queries. If this is something that you are going to need to re-use, you can set up a simple Macro to run each query, so you can run the whole process with a single click.

Post back here with any specific questions you may have. But take a look at that link first, as that shows you how to set up Update Queries.