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?
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])))
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 FyeCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.