Using a nested function

[andrea]

I am very new to MS Office.
I have a nested function that calculates cost on a sliding scale in Excel. I want to put that function in an Access Form.
I have tried copy and paste the function into "control data source" on the form. This does not seem to work.
Here is an example of the function in Excel
=IF(H2>(D2*200),IF(H2<=(D2*400),(((H2-(D2*200))*0.89))+((D2*100)*0.36)))
I did change the "=if" to "=IFF"
Any help is appreciated.

 

[andrea]

oops- I meant I changed to"=IIF

 

[Douglas J. Steele]

IIf has three parts: the boolean expression, what to do if the boolean
expression is true, and what to do if the boolean expression is false.

You don't have instructions on what to do if either of the boolean
expressions are false.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am very new to MS Office.
I have a nested function that calculates cost on a sliding scale in Excel.

I want to put that function in an Access Form.

I have tried copy and paste the function into "control data source" on the

form. This does not seem to work.

 

[andrea]

If the statement is false I need the value to be zero. Can you please show me how and where to add this
Thank Yo

 

[zfidjili]

I am very new to MS Office.
I have a nested function that calculates cost on a sliding scale in Excel.

I want to put that function in an Access Form.

I have tried copy and paste the function into "control data source" on the

form. This does not seem to work.

 

[Wayne Morgan]

Andrea, with what you have it is a little hard to tell what you want where.

=IF(H2>(D2*200),IF(H2<=(D2*400),(((H2-(D2*200))*0.89))+((D2*100)*0.36)))<

For (H2>(D2*200)) what do you want if this is True and what do you want if
this is False?
For (H2<=(D2*400)) what do you want if this is True and what do you want if
this is False?

The way you have structured this, I would assume that one of the 2 answers
to the first question should be that you want to make the check for the
second question.

--
Wayne Morgan
MS Access MVP

If the statement is false I need the value to be zero. Can you please

show me how and where to add this.

 

[andrea]

Wayne
You are correct, i
(H2>(D2*200)) is True, I want it to check to see if (H2<=(D2*400)) is True. If true I want
(((H2-(D2*200))*0.89))+((D2*100)*0.36))
If False- then I want zero
Thank you so much for your help

 

[Douglas J. Steele]

Your original statement was:

IIf(
H2>(D2*200),
IIf(
H2<=(D2*400),
(((H2-(D2*200))*0.89))+((D2*100)*0.36)
)
)

Assuming you need zeros on both false sides, that would change to

IIf(
H2>(D2*200),
IIf(
H2<=(D2*400),
(((H2-(D2*200))*0.89))+((D2*100)*0.36),
0
),
0
)

or

IIf(H2>(D2*200),IIf(H2<=(D2*400),(((H2-(D2*200))*0.89))+((D2*100)*0.36), 0),
0)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

If the statement is false I need the value to be zero. Can you please

show me how and where to add this.

 

[andrea]

Thank You so much!!!