Proper syntax for replace()

[JimDantin]

When using the replace() function, is it possible to refer to a variables in the find and replace parameters?

Replace(«string», «find», «replace»)

I can use a formula like this, where the replacement text is a table field.
Replace([textin],"Find this text string",[cname])

What I want to do is get the text to find, and the variable NAME to use for replacement from a table like this:

txtfind replacefieldname

"<cname>" [customername]
"<shipaddress>" [shipaddress]
"<custnumber>" [cnumber]


Imagine a query that would replace a series of placeholders, set up as some text between <> brackets, with the data from a query or table that contains the three fields shown. It's like a mailmerge function.

I can't figure out the proper syntax that would refer to the txtfind and replacefieldname variables in the replace() function.

This is part of a larger project.

Thanks
Jim

[Norie]

How will you determine which pair to use?

[JimDantin]

I have a query that selects the proper data (customer). This function will look at each text record for as many times as there are placeholder records --

This works for one of the placeholder names:

UPDATE tblData, tblText SET tblText.textout = Replace([textin],"<cname>",[cname]);

I can save and run similar queries for each of the other placeholder names, create a macro that runs each query, and it works just fine. But if I add a new placeholder I need to create a new query and then add it to the macro that calls them all. Works, but it's an ugly approach.

I want a solution that updates itself regardless of the number of placeholders. There may be other solutions - and I'd welcome ideas.

If I can get the function to work the way I want, then one query will generate all the updates and would not have to be modified if the user added new placeholder records to the lookup table (tlkpTokens):

UPDATE tblData, tblText, tlkpTokens SET tblText.textout = Replace([textin],{tlkpTokens.txtfind},{tlkpTokens.replacefieldname});

That query would create {number of text records} X {number of token records} records and would, in effect, look at each text record multiple times, replacing one placeholder token with the proper data from tblData each time.

[hansup]

As I understand your description, you have a table with a text field, and you're planning to perform a series of string replacements on that field.

Instead of using an UPDATE statement for each replacement, which requires the db engine to read the text field and write the new value back each time, I'll suggest you create a VBA procedure to read the text field into a variable, perform all your replacements against the variable, then finally write the changed variable back to the text field. That approach would involve a single database read/write cycle for each text field.

I realize this suggestion only touches on one piece of your question. However, I don't understand the rest of the question or how this piece would fit into it. Showing us a brief set of sample data might help clarify your goal. I'm thinking here especially of the tlkpTokens lookup table.

Also, I'm curious why you apparently want to drive this operation from a query, instead of directly calling a VBA procedure.

[JimDantin]

Well -- I'm a lot better with queries than VBA for one thing!

I'll try anything -- so far whatever I've searched for doesn't exist.

Here's a simple example of the text string:

Dear <cname>, we have shipped a package to you at <shipaddress>. It was charged to your account number <cnumber>

Really, the text strings are that simple. The key is to be able to handle a range of placeholders that will grow -- I need the code (or query) to be able to handle additional placeholders WITHOUT having to modify the code or query. The user will add entries to the lookup table, and will create new text strings with the embedded placeholders. I have a query that will extract the appropriate data from the other tables based on fields on a form.

What else do you need?

[hansup]

To confirm, is this what tlkpTokens looks like?

txtfind replacefieldname
<cname> [customername]
<shipaddress> [shipaddress]
<custnumber> [cnumber]

And the values stored in replacefieldname ... those are the names of fields in a query?

If that is correct can you show us the SQL for that query and a brief representative sample of its result set?

Does the query return one row, or more than one? If more than one, are all the query rows matched up with the same row in tblText? If not the same tblText row, how do you match them?