Formula Help

Gordon Hughes
Gordon Hughes used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

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 Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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} )

mlmcc
Gordon HughesDirector

Author

Commented:
Hi
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

Gordon
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Gordon HughesDirector

Author

Commented:
Unless!!!
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

Gordon
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try it this way

{MembersDetails.Membership Main}  = "Tennis Hon"
OR
(
{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}
)

mlmcc
Gordon HughesDirector

Author

Commented:
The last formula from mimcc is spot on
Appreciate all your efforts and fast responses
Thanks
Gordon

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial