Formula Help

Help with Formula

I have the following formula:-
{MembersDetails.Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Hon", "Tennis Ovr65"] and
{MembersDetails.ExpiryDate} >= {?Expiry Date is Greater Than} or isnull ({MembersDetails.ExpiryDate})

There are some of the {MembersDetails.Membership Main} with no expiry date so added or isnull ({MembersDetails.ExpiryDate}) to the formula but it give me all the members with no expiry date , but only want to see the membership main categories in the first part of the formula

How do I do this?
Gordon HughesDirectorAsked:
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.

Raghavendra HullurSoftware DeveloperCommented:

Per my understanding, you want to see the mentioned membership categories with Expiry Date greater than specified parameter date or those with no expiry date, is it?

If so, change the formula as below:

{MembersDetails.Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Hon", "Tennis Ovr65"] and
({MembersDetails.ExpiryDate} >= {?Expiry Date is Greater Than} or isnull ({MembersDetails.ExpiryDate}))

Let me know if that's not the case.
Mike McCrackenSenior ConsultantCommented:
YOu probably need to reverse the date comparison.  Crystal will fail if a NULL field is used in a comparison other than IsNull

{MembersDetails.Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Hon", "Tennis Ovr65"] and
 (isnull ({MembersDetails.ExpiryDate}) OR {MembersDetails.ExpiryDate} >= {?Expiry Date is Greater Than} )

Gordon HughesDirectorAuthor Commented:
I appreciate your efforts but I think I have to have an expiry date in the database, as if the null option is used it brings out of date members onto the report

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Gordon HughesDirectorAuthor Commented:
The group where there are no expiry dates is the Tennis Hon, theses do not have an expiry date
So can the formula show the other members with the selected expiry date greater than with all the Tennis Hon members

Mike McCrackenSenior ConsultantCommented:
Try it this way

{MembersDetails.Membership Main}  = "Tennis Hon"
{MembersDetails.Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Ovr65"] and {MembersDetails.ExpiryDate} >= {?Expiry Date is Greater Than}


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
Gordon HughesDirectorAuthor Commented:
The last formula from mimcc is spot on
Appreciate all your efforts and fast responses
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.