Trying to translat Excel calculated field syntax to Access syntax

I'm trying to translate this from an Excel worksheet:

=IF(C11<1,"",IF(E11>$M$7,(((1-$M$7)*C11)-(((E11-$M$7)*$M$8)*C11)),((1-E11)*C11)))

to an Access form field calculation.

Note that in the Access form these are the cooresponding field names:

C11 = txtGrossCWT
E11 = txtFM
M7 = Header txtFMMax
M8 = Header txtDisSchd2

I don't even know where to begin.  Any help would sure be appreciated.
SteveL13Asked:
Who is Participating?

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

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

Gustav BrockCIOCommented:
Replace IF with the function IIf and the cell references with your control names.
0

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
NorieAnalyst Assistant Commented:
Are all these values in the same record?
0
SteveL13Author Commented:
The top 2 are in the same record.  The bottom 2 have to come from the Main Form.  The datasheet form is a sub-form in the main form.
0
Dale FyeOwner, Developing Solutions LLCCommented:
are you attempting to do this as the value in a control source, or doing it in a query?

So you have a couple of issues,
1.  you must replace the Excel IF( ) statement with the Access IIF( )
2.  I have assumed that the values for cells M7 and M8 are actually controls in your form, so I have referred to them as me.txtFMMax and me.txtDisSchd2

=IIF(txtGrossCWT<1,NULL,IIF(txtFM>me.txtFMMax,(((1-me.txtFMMax)*txtGrossCWT)-(((txtFM-me.txtFMMax)*me.txtDisSchd2)*txtGrossCWT)),((1-txtFM)*txtGrossCWT)))

Personally, I'd create a function for that so that you can document why you are performing the calculations the way you are.
0
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.