SQL.AddCriteriaField

rbuser

Hi there,

I'm using the "SQL.AddCriteriaField" to add criteria to limit the users
access to certain data. It looks something like this:

SQL.AddCriteriaField(SQL.SelectTables[i], 'CUSTOMERS' ,
ppOperatorTodaOperator(soInList), sCustomerList);

"sCustomerList" is a string that concatenates all the customers the user has
access to, and it might look something like this:

Brian, Bill, Carolyn, Robert

However, some of these customers may have a comma in their name, so this
string will not work properly because it looks like there are two separate
values in the list.
Brian
Jones, Bill
Carolyn
Robert
= Brian, Jones, Bill, Carolyn, Robert

Does anyone have an idea for a workaround with this one? It worked
perfectly until I came across a customer with a comma in her name. Existing
data prevents me from controlling the data entry to avoid this problem.

Thanks.

Leah

digitalmetaphors

Can you parse out commas and add quotes to the sCustomerList string variable
so that the SQL ignores the comma and lets it treate the "Jones, Bill" as
one entry?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I'm not sure I understand. Once I add the criteria to the table,
ReportBuilder adds quotes to the sCustomerList so when the SQL is executed
it looks like:
'Brian', 'Jones', 'Bill', 'Carolyn', 'Robert'.

Adding quotes to my sCustomerList as well result in double quotes
surrounding all values, as well as single quotes between Jones and Bill:
''Brian'', ''Jones', 'Bill'', ''Carolyn'', Robert''

An SQL error ("missing right parenthesis") is generated.

Leah

rbuser

Hi ... I found a solution. I substituted a different character for the
comma in the customer name (Jones. Bill) when adding the criteria value, and
then replaced it with the comma in the MagicSQLText. It's not pretty, but
hey, it works.

Thanks for your help,

Leah

digitalmetaphors

Aren't you in control of the lsCustomerList string variable? Where is that
value coming from? I was thinking something similiar to what is shown below
would work if you made 'Bill,' 'Jones' into 'Bill, Jones'?

{get SQL object}
GetSQLObject(ppReport1, lSQL);

{if criteria have not been created, then add them}
lSQL.ClearCriteria;

lsCustomerList := 'Bill, Jones';

lSQL.AddCriteriaField(lSQL.SelectTables[0], 'Contact' , dacoInList,
lsCustomerList);



Cheers,

Jim Bennett
Digital Metaphors

rbuser

ReportBuilder will automatically separate Bill and Jones, because the
'inlist' type is looking for a list separated by commas. So lsCustomerList
would end up looking like ''Bill', 'Jones''.

Leah