Solved

Parameters - bring all values back

Posted on 2014-09-22
6
111 Views
Last Modified: 2014-09-23
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
0
Comment
Question by:halifaxman
6 Comments
 
LVL 22

Expert Comment

by:Ido Millet
ID: 40336878
Change "and" to "OR"
0
 

Author Comment

by:halifaxman
ID: 40338512
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 40338717
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 100

Expert Comment

by:mlmcc
ID: 40338990
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
 

Author Closing Comment

by:halifaxman
ID: 40339169
Thanks - much appreciated
0
 
LVL 34

Expert Comment

by:James0628
ID: 40339388
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now