Using a nested function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


andrea said:
I am very new to MS Office.
I have a nested function that calculates cost on a sliding scale in Excel.
Click to expand...
I want to put that function in an Access Form.
I have tried copy and paste the function into "control data source" on the
Click to expand...
form. This does not seem to work.
 
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
 
andrea said:
I am very new to MS Office.
I have a nested function that calculates cost on a sliding scale in Excel.
Click to expand...
I want to put that function in an Access Form.
I have tried copy and paste the function into "control data source" on the
Click to expand...
form. This does not seem to work.
 
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)))<
Click to expand...

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


andrea said:
If the statement is false I need the value to be zero. Can you please
Click to expand...
show me how and where to add this.
 
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
 
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)


andrea said:
If the statement is false I need the value to be zero. Can you please
Click to expand...
show me how and where to add this.
 
Back
Top