Symptom
-
what is the proper syntax for the "IN" operator with parameters
-
"IN" operator does not return expected values
Environment
-
Crystal Reports 2008
-
SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
-
Create a report of Xtreme Sample Database and "Customer" table
- Add a single value string parameter
- Add records selection formula: {Customer.Contact First Name} IN {?My Parameter}
- Refresh the report and enter "Alexander" for the parameter
- It brings records with the following values for the First Name: "Alex", "Alexa" and "Alexander"
Cause
-
The formula works as expected. Formula scans the parameter string for the match and returns TRUE even if the field just a part of the parameter string.
Resolution
- If 100% match is required then use one of the following syntax for records selection formula:
- {Customer.Contact First Name} = {?My Parameter}
- {Customer.Contact First Name} IN [{?My Parameter}]
Note: Square barckets makes the parameter as a part of an Array, so it compares the field to a member of Array.
See Also
-
Click Help menu in Crystal Reports and search for "IN ARRAY" and "ARRAY DATA TYPES" articles
Keywords
IN, LIKE, function, wrong results , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To
Product
SAP Crystal Reports 2016