Help with another formula

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
you may try:

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

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

Open in new window

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The first one should work.  The second formula will fail for NULL values.

mlmcc
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
perhaps this will work for 2nd suggestion?

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

Author

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 Lead

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

Author

Commented:
Hi All

I have now used the formula without the "" part

Gordon
Gordon HughesDirector

Author

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