I have a set expression that creates a union of two states. The expression must also cover the situation where the data contains a space, a left parentheses, and a right parenthesis. I can make the expression work with a space, but am having trouble extending it.
Below is an example of what is working:
Replace('(' & Concat({$ + S4} Product, '|') & ')',' ','?')
In this case, I am creating a successful union between the default state, and S4, and inserting a pipe character between the entries. I am also replacing any spaces that are found with a question mark. Altogether, this expression takes selections made in one state (S4), and adds them to selections already in place in another state ($). This works perfectly.
My data also contains a right and left parenthesis. When I try to extend this expression, I am entering:
Replace(Replace(Replace('(' & Concat({$ + S4} Product, '|') & ')',' ','?'),'(','?'),')','?')
The syntax editor indicates the expression is OK, but it does not work. Specifically, it no longer moves selections between the states.
As a debugging step, I've tried putting the below example into a text box. It works perfectly.
Replace(Replace(Replace(Product,' ','?'),'(','?'),')','?')
When I do this, I get the product names, with spaces and parenthesis replaced by question marks.
Anybody have any ideas why the middle example does not work?
gary
