Help with adding a field.value to another field.value

[wubbit]

I have a client who needs to be able to tweak some entries that are his "standard" answers automatically.
For example:
in table INCLUDES I have column MACRO which contains "We will complete the job within considerable time and adhere to {COMPANY NAME}'s policies and standards...."


He would like the database to replace the {COMPANY NAME} automatically from the table CUSTOMERS. I am not able to figure this out, been about four years since I had to use Access (MySQL is my choice, his was Access).

Any help to point me in some direction would be greatly appreciated!

Thanks in advance.

[Rawb]

SELECT
  Replace([INCLUDES].[MACRO], "{COMPANY NAME}", [CUSTOMERS].[COMPANY NAME]) AS [CUSTMACRO]
FROM 
  [INCLUDES]
INNER JOIN
  [CUSTOMERS]
  ON
    [INCLUDES].[CUSTOMER ID] = [CUSTOMERS].[ID]
WHERE
  [CUSTOMERS].[ID] = 1;

Assuming your INCLUDES and CUSTOMERS Tables are linked by the customer ID, the above Query would return a single field called CUSTMACRO with "{COMPANY NAME}" replaced with the actual company's name for Customer ID 1.

[wubbit]

Thanks for the assistance, I am going to shoot for a little less complicated. I have a query that pulls all the fields I need to make this happen, but need to generate a If, Then clause to replace the different text that is modified by the user. I think that it can be done, but should this be generated as a Macro of on the forms and reports in the textboxes? Like I said it has been quite some time since I had to use Access.
I was looking into using this clause for the replace also, it keeps giving an error though.

=Replace([Macros_QUR].[Includes]![macro_part1],"{COMPANY NAME}",[Macros_QUR].[Customers]![company_name])

Thanks again for the reply, and any help on this new item

[Rawb]

I understand. It took me forever to really understand SQL enough to really figure out JOINs and SubQueries. I'll tell ya though, they're really handy once you figure 'em out!

As for your Code there, is "Macros_QUR" the name of your database? Or is it a Form/Report field? Assuming "Includes" and "Customers" are the names of your tables and "macro_part1" and "company_name" are fields, then your syntax should be more like this:

=Replace([Includes].[macro_part1], "{COMPANY NAME}", [Customers].[company_name])

You shouldn't have to include the DB name itself.

[wubbit]

Macros_QUR is the query that is populating the form and reports. I thought that it had to be called when using a query...

[Rawb]

If the Query is set up as the Form's Record Source, then you don't need to use the Query name. And if it's not, you may want to see if you can modify your Record Source Query to provide the company_name and macro_part1 fields so you can reference them easier.

[wubbit]

Thanks! That really helped me understand this more. I got it to work finally. I guess I over thought the whole thing again.

Thanks for the assistance!