Using Operator “IN range” and a parameter?

[Bennie47250]

Using Crystal version 7.0

Is it possible to create a parameter that will allow for the entry of a range?

For example, at the prompt would like to enter ether a 1 or a 2 or 1,2.

I have tried:
{OpNumbers.Plant} in [{?PlantNumber}]
and I will enter 1,2. But the SQL it passes is WHERE OpNumbers.Plant = 1,2 and I get nothing as there are no values in the database of 1,2.

If I just enter a 1 or just a 2 the report returns the correct values

Thanks
Bennie

 

[vidru]

Do you have the option to set your parameter field to 'Allow multiple values' in CR 7 (I should think so)? If so, set it. Then a record selection formula like...

{OpNumbers.Plant} in {?PlantNumber}

... would work for you.

-dave

 

[dgillz]

Or if you want to hard code this formula:

{OpNumbers.Plant} in 1 to 2



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[Bennie47250]

When I follow vidru suggestion, the SQL that is passed is WHERE
(OpNumbers."Plant" = 2 OR
OpNumbers."Plant" = 1)

So I'm not getting the benefit of using the IN range.

 

[vidru]

'IN' and 'OR' are processed the same way by the server. For example:

SELECT * FROM Clients WHERE state = 'CA' OR state = 'IN' OR state = 'MD'

... is the same as

SELECT * FROM Clients WHERE state IN('CA', 'IN', 'MD')

If you're using SQL Server, you can run some tests and check out the execution plans. You'll see that the IN gets converted to multiple OR's.

-dave

 

[Bennie47250]

dave, I see what you’re saying, thanks for pointing that out.

I'm still having problems so I'm going to tackle it another way.

Thanks

 

[lbass]

Did you set the parameter to allow multiple values and choose "Discrete and range values"? I think you could just use:

{table.number} = {?parm}

-LB

 

[Bennie47250]

I'm using Ver 7.0 of Crystal and it only allows using ether Discrete or Range Values not both.

I have addressed this problem by using the between operator.

Thanks for the assistance.