IIF statement - error message

Access 2013 - Report Field:
I am trying to place an iif statement that will state "Okay" if the value is within the parameter, or provide the value needed to ensure compliance.

My current formula is:
=iif([Avg Of NSSI/NCSI])>=(Forms![CSIReportEngineForm]![SalesTier1]),"Okay",(((Count([NSSI/NCSI])*[Forms]![CSIReportEngineForm]![SalesTier1])-(Count([NSSI/NCSI]))*(Avg([NSSI/NCSI])))/(1000-[Forms]![CSIReportEngineForm]![SalesTier1]))

I receive the "The expression you entered has a function containing the wrong number of arguments.

Thanks all!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
=iif([Avg Of NSSI/NCSI]>=(Forms![CSIReportEngineForm]![SalesTier1]),"Okay",(Count([NSSI/NCSI])*[Forms]![CSIReportEngineForm]![SalesTier1])-(Count([NSSI/NCSI])*Avg([NSSI/NCSI]))/(1000-[Forms]![CSIReportEngineForm]![SalesTier1]))

Think that's it...pretty messy.  you'd be better setting up a procedure and calling that.


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
Shaun KlineLead Software EngineerCommented:
=iif([Avg Of NSSI/NCSI]) <-- That closing parenthesis might be the problem.

IIf([Avg Of NSSI/NCSI]>=[Forms]![CSIReportEngineForm]![SalesTier1];"Okay";(Count([NSSI/NCSI])*[Forms]![CSIReportEngineForm]![SalesTier1])-(Count([NSSI/NCSI])*Avg([NSSI/NCSI])/(1000-[Forms]![CSIReportEngineForm]![SalesTier1])))
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
try this

=iif(([Avg Of NSSI/NCSI])>=(Forms![CSIReportEngineForm]![SalesTier1]), "Okay"
,((Count([NSSI/NCSI])*[Forms]![CSIReportEngineForm]![SalesTier1])-(Count([NSSI/NCSI])* Avg([NSSI/NCSI])))/(1000-[Forms]![CSIReportEngineForm]![SalesTier1]))
Dale FyeOwner, Developing Solutions LLCCommented:
I'm assuming this is the control source of a control in the footer of a form.  Otherwise the syntax: Count([NSSI/NCSI]) and Avg([NSSI/NCSI]) make no sense.  Because you are using the Count([NSSI/NCSI]) twice, you can commute that and simply multiply that value times the difference in the Forms!...![SalesTier1] and the Count([NSSI/NCSI]).

=iif([Avg Of NSSI/NCSI]>= Forms![CSIReportEngineForm]![SalesTier1],
      (Count([NSSI/NCSI]) * ([Forms]![CSIReportEngineForm]![SalesTier1] - Avg([NSSI/NCSI])))/(1000-[Forms]![CSIReportEngineForm]![SalesTier1]))

You need to count your parenthesis and make sure they match.
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>
Syntax errors rarely show up "suddenly".

Always start with a simple expression, ...then build on it,
This way you are only troubleshooting the last change you made (that throws the error)
...instead of creating an extremely complex expression (in one fell swoop) and asking: "why doesn't it work?"

For example, an IIF takes three arguments:
IIF ( boolean_expression, true_value, false_value )
So does something like this work?
=iif([Avg Of NSSI/NCSI]>=Forms![CSIReportEngineForm]![SalesTier1],"Okay","Not OK")

Next, evaluate each of your expressions on their own, to see if they return valid values
Count([NSSI/NCSI]) * [Forms]![CSIReportEngineForm]![SalesTier1]

Then set the framework of your IIF like so:
IIF(     ,     ,    )
...then like this:
IIF  (  ( )  ,  ( )  ,  ( )  )
Then insert each of your *valid* expressions, following basic Parenthetical rules

This all being said, ...I also agree with Jim, ...that when you need a IIF expression this complex, ...you should consider a code solution.

With a code solution you can:
-Structure the code to be more easily readable
-Take advantage of all of VBA troubleshooting utilities
-Add custom comments
-Make the code "portable"


DGWhittakerAuthor Commented:
Thanks All!
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
Microsoft Access

From novice to tech pro — start learning today.