Parameters - bring all values back

I am using CR2008

I using a SQL command in my report.

I have a number values in a parameter and it allows multiple values

The synatx is as follows

where (MA_SchemeID IN {?Scheme} and {?Scheme}  = 0)

I am using {?Scheme}  = 0 to bring back all values, but this is not working

Can anybody help with the correct syntax?

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Change "and" to "OR"
0
Mark WilsonBI DeveloperAuthor Commented:
Thanks for the answer

I have changed it to or and get the following error message

An expression of non-boolean type specified in a context where a condition is expected near a ','
0
James0628Commented:
FWIW, Ido was correct about needing OR there.  As for the error ...

 If the parameter allows multiple values, then I would guess that {?Scheme}  = 0 is getting translated into something like (1,2,3) = 0, and that's what is causing the error.

 Try this

where (MA_SchemeID IN {?Scheme} OR 0 IN {?Scheme})

 Note that if you enter values for the parameter like 1, 2, 3 and 0, that would count as "all", because one of the parameter values is 0.

 Also, now that I think about it, if you think that you might be using the "all" option a lot, it might be more efficient to put the "all" test first:

where (0 IN {?Scheme} OR MA_SchemeID IN {?Scheme})

 It probably wouldn't make much difference.  It just occurred to me that it might be more efficient.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

mlmccCommented:
If that doesn't help, post the new formula.

If this is part of the selection formula then please post the full formula.

mlmcc
0
Mark WilsonBI DeveloperAuthor Commented:
Thanks - much appreciated
0
James0628Commented:
You're welcome.  FWIW, Ido really deserves some of the points, because he posted part of the solution.  If you like, you can ask to have the question re-opened and then split the points between us.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.