Modify parameter before sending to command 1

[JennL]

We have a report that does a recursive SQL lookup based on part number(s) entered into a parameter value. The users are supposed to put a single quote around the part numbers but tend to forget then call because the report is busted.

It would be easy to fix if I could just have the SQL Command use a formula instead of a parameter, but I haven't been able to. All I want to do is add single quotes to the parameter and replace any commas in the parameter string with a single quote + comma + single quote. I tried doing that using both quotes and char in the command but am not returning results.

Does anyone know how to get this to work?

The formula that I know would work - except the command won't read a formula:
if instr({?PartNum},"'")>0 then
{?PartNum}
else
"'" + replace(replace({?PartNum},",","','")," ","','") + "'"

What I tried in the command:
in (char(39) + replace({?PartNum},',', char(39) + ',' + char(39)) + char(39))

 

[lbass]

Since you are trying to feed multiple values to a command parameter, you might consider the approach in the following faq766-6779.

-LB