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?
 
Gustav BrockConnect With a Mentor CIOCommented:
Replace IF with the function IIf and the cell references with your control names.
0
 
NorieVBA ExpertCommented:
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 FyeConnect With a Mentor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.