Formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1,0))
This formula tells me that is not only works with null. My goal is to have
all accounts that have 0 charges that are not with financial code 222,333, or
555 to show. Im not sure if I am missing any ( ) either. Could someone
please advise on how to correct this formula? Thank you in advance.
 
Ryan said:
Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1,0))
This formula tells me that is not only works with null. My goal is
to have all accounts that have 0 charges that are not with financial
code 222,333, or 555 to show. Im not sure if I am missing any ( )
either. Could someone please advise on how to correct this formula?
Thank you in advance.
Click to expand...

Use the not equals operator <>. In your case with multiple values you can
also use Not In()

Missing: Count(IIf([charges]=0 Or [FinancialCode] Not In(222, 333,
555),1,0))
 
Thank you for your help. I did have to change the ,1,0)) to ,1,null)). The
Not in was a big help!

Rick Brandt said:
Ryan said:
Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1,0))
This formula tells me that is not only works with null. My goal is
to have all accounts that have 0 charges that are not with financial
code 222,333, or 555 to show. Im not sure if I am missing any ( )
either. Could someone please advise on how to correct this formula?
Thank you in advance.
Click to expand...

Use the not equals operator <>. In your case with multiple values you can
also use Not In()

Missing: Count(IIf([charges]=0 Or [FinancialCode] Not In(222, 333,
555),1,0))
Click to expand...
 
The iif seems useless. A COUNT will COUNT a 0 or a 1 as being ONE value. You
may try SUM, or, if you really want to keep COUNT, use NULL:

Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1, NULL ))



Hoping it may help,
Vanderghast, Access MVP
 
and sure, like said by Rick, use <> instead of is not, here.

Vanderghast, Access MVP

Michel Walsh said:
The iif seems useless. A COUNT will COUNT a 0 or a 1 as being ONE value.
You may try SUM, or, if you really want to keep COUNT, use NULL:

Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1, NULL ))



Hoping it may help,
Vanderghast, Access MVP


Ryan said:
Missing: Count(IIf([charges]=0 Or [FinancialCode]is not 222 Or
[FinancialCode]is not 333 Or [FinancialCode]is not 555,1,0))
This formula tells me that is not only works with null. My goal is to
have
all accounts that have 0 charges that are not with financial code
222,333, or
555 to show. Im not sure if I am missing any ( ) either. Could someone
please advise on how to correct this formula? Thank you in advance.
Click to expand...
Click to expand...
 
Back
Top