Help with another formula

I have a report with the following formula
if isnull ({MembersDetails.Email Usage}) or {MembersDetails.Email Usage} = ""  then 'Not recorded' else ({MembersDetails.Email Usage})

I have data in the database of Null , blank or with Yes or No

The formula on give a return of Not Recorded when there is a Yes or No

Gordon
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.

Ryan ChongSoftware Team LeadCommented:
you may try:

if isnull ({MembersDetails.Email Usage}) or {MembersDetails.Email Usage} = ""  or {MembersDetails.Email Usage} = "No" then 'Not recorded' else ({MembersDetails.Email Usage})
Ryan ChongSoftware Team LeadCommented:
or in another way round:

if {MembersDetails.Email Usage} = "Yes" then ({MembersDetails.Email Usage}) else 'Not recorded'

Open in new window

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
Mike McCrackenSenior ConsultantCommented:
The first one should work.  The second formula will fail for NULL values.

mlmcc
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Ryan ChongSoftware Team LeadCommented:
perhaps this will work for 2nd suggestion?

if "Yes" = {MembersDetails.Email Usage} then ({MembersDetails.Email Usage}) else 'Not recorded'
Gordon HughesDirectorAuthor Commented:
Hi
The current formula is:-
if isnull ({MembersDetails.Email Usage}) or {MembersDetails.Email Usage} = ""  then 'Not recorded' else ({MembersDetails.Email Usage})

The value in the field is either NULL, Yes or No

Am not sure why or {MembersDetails.Email Usage} = ""  is part of the formula?

Gordon
Ryan ChongSoftware Team LeadCommented:
Am not sure why or {MembersDetails.Email Usage} = ""  is part of the formula?
well, this was included in your question : )

have you tried suggestions above? any luck so far?
Mike McCrackenSenior ConsultantCommented:
You need to test for NULL first.  if a NULL value is tested for other than IsNull the result is undetermined since the formula errors out.

If what has been provided doesn't work, please show what you want for each possible value and what you are getting now
If you don't need the empty string test it can be taken out
if isnull ({MembersDetails.Email Usage}) then 
       'Not recorded' 
else 
    ({MembersDetails.Email Usage})

Open in new window


mllmcc
Gordon HughesDirectorAuthor Commented:
Hi All

I have now used the formula without the "" part

Gordon
Gordon HughesDirectorAuthor Commented:
Thanks for all your support
Gordon
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
Databases

From novice to tech pro — start learning today.