Combine multiple if statements in one text box

[DRMOB]

On a form I have a text box that I want to populate with a calculated number
from another form. The number I need to see depends on 2 check boxes
"RevisedGrant" and "Waive10Pct"
Can anyone tell me what is wrong with the code. I have had it a number of
diff way. Enclosing the iff in () etc. This problem is taking me much too
long to figure out. Please Help!!

=IIf([RevisedGrant]=True And [Waive10Pct]=True,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Revised
Less 10Pct]), elseIf([RevisedGrant]=True And [Waive10Pct]=False,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![RevisedTotalApproved]),
elseIf([RevisedGrant]=False And [Waive10Pct]=False,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Total
Approved]) elseIf([RevisedGrant]=False And [Waive10Pct]=True,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Less
10Pct])

 

[mcescher]

On a form I have a text box that I want to populate with a calculated number
from another form.  The number I need to see depends on 2 check boxes  
"RevisedGrant" and "Waive10Pct"
Can anyone tell me what is wrong with the code.  I have had it a number of
diff way. Enclosing the iff in () etc. This problem is taking me much too
long to figure out.  Please Help!!

=IIf([RevisedGrant]=True And [Waive10Pct]=True,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Revised
Less 10Pct]), elseIf([RevisedGrant]=True And [Waive10Pct]=False,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![RevisedT­otalApproved]),
elseIf([RevisedGrant]=False And [Waive10Pct]=False,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Total
Approved]) elseIf([RevisedGrant]=False And [Waive10Pct]=True,
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Less
10Pct])

You can't use ElseIF as part of an IIF. Either you'll need to nest
the statement =iif(a = b, "Test1", iif(b=c, "Test2", iif(c=d,
"Test3","Failed all")))

or you could write a function to do the calculation.

Hope this helps,
Chris M.

 

[Pat Hartman]

elseIf is not used with IIf() because IIf() is a function. Nested ifs
continue with IIf()

This is so much easier to understand if you do it with VBA. You need to add
this code to an event procedure. I don't know which one. Probably the
form's AfterUpdate event.

You also have a design flaw if you need to do this at all. How are you
ensuring that the subform you are referring to is opened to the correct
record?


If([RevisedGrant] =True
If [Waive10Pct]=True Then
Me.SomeField =
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Revised >
Less 10Pct])
else
Me.someField =
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![RevisedTotalApproved]),
End If
Else
If [Waive10Pct]=True Then
Me.SomeField =
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Less >
10Pct])
Else
Me.SomeField =
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Total
Approved])
End If
End If

 

[John W. Vinson]

On a form I have a text box that I want to populate with a calculated number
from another form. The number I need to see depends on 2 check boxes
"RevisedGrant" and "Waive10Pct"
Can anyone tell me what is wrong with the code. I have had it a number of
diff way. Enclosing the iff in () etc. This problem is taking me much too
long to figure out. Please Help!!

Try using either a custom function as suggested, or use the Switch() function
instead of nested IIF's. Switch() takes arguments in pairs; the pairs are read
from left to right. When Access finds a pair for which the first element is
TRUE it returns the value of the second element. So you could use something
like

Switch([RevisedGrant] AND [Waive10Pct],
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Revised
Less 10Pct],
[RevisedGrant] AND NOT [Waive10Pct],
Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![RevisedTotalApproved],
<etc>


John W. Vinson [MVP]