# Formula Help

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® is a registered trademark of EXPERTS EXCHANGE®
Software Developer

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.
Senior 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
Director

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
Director

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
Director

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

Do more with