There are two types of procedures: Subroutines and Functions.
Access help defines a Function procedure (aka a user defined function) as:
A procedure that performs a specific task within a Visual Basic program and
returns a value. A Function procedure begins with a Function statement and
ends with an End Function statement.
Commission: IIf([Accrual Monthly tbl].[File #]="000097",[Loan
Amount]*0.0065,IIf([Accrual Monthly tbl].[File #]="000108",[Loan
Amount]*0.006,IIf([Accrual Monthly tbl].[File #]="000060" Or [Accrual Monthly
tbl].[File #]="000058" Or [Accrual Monthly tbl].[File
#]="000110",IIf([sumofloan Amount]>=3000000,[loan
amount]*0.006,IIf([sumofloan Amount]>=1000000,[loan amount]*0.0055,[loan
amount]*0.005)),0)))
In the examples (untested!) below , I converted your nested IIF() syntax to
a UDF. Which is easier to read and/or change??
The nice thing about functions is that you can use them is a query, in code
or as a control source of a textbox.
In your case you would call the function like:
in your query:
Commission: GetComm( [File #], [sumofloan amount], [loan amount])
or maybe like this (not sure about this one):
Commission: GetComm( [Accrual Monthly tbl].[File #], [sumofloan amount],
[loan amount])
as a control source in a textbox:
= GetComm( [File #], [sumofloan amount], [loan amount])
You can use hard coded values: (not very useful - always returns the same $$)
Commission: GetComm( "000097", 1500000, 900000)
You can use variables as long as they are the correct data type:
MyMoney = GetComm( Monkey, MotorHome, XXX )
OK, you get the picture...... Here is the code. Put it in a standard module:
Remember, these are the same, except
Example 1 uses If..Then..Else syntax and
Example 2 uses If...Then...ElseIf syntax.
(One more time, this is untested code - WATCH for line wrap)
Use one or the other....
'--------------Example 1 ---------------------------------
' this should be on one line
Public Function GetComm(pFileNum As String, pSumOfLoanAmt As Currency,
pLoanAmt As Currency) As Currency
Dim tmpComm As Currency
' set commission to zero $
tmpComm = 0
' check the file #
If pFileNum = "000097" Then
tmpComm = pLoanAmt * 0.0065
End If
' check the file #
If pFileNum = "000108" Then
tmpComm = pLoanAmt * 0.006
End If
' check the file #
If pFileNum = "000060" Or pFileNum = "000058" Or pFileNum = "000110" Then
' is pSumOfLoanAmt >= $3,000,000?
If pSumOfLoanAmt >= 3000000 Then
tmpComm = pLoanAmt * 0.006
End If
' is pSumOfLoanAmt >= $1,000,000?
If pSumOfLoanAmt >= 1000000 Then
tmpComm = pLoanAmt * 0.0055
Else ' pSumOfLoanAmt < $1000000
tmpComm = pLoanAmt * 0.005
End If
End If
' return the commission
GetComm = tmpComm
End Function
'--------------Example 2 ---------------------------------
' this should be on one line
Public Function GetCommission(pFileNum As String, pSumOfLoanAmt As Currency,
pLoanAmt As Currency) As Currency
Dim tmpComm As Currency
' set commission to zero $
tmpComm = 0
' check the file #
If pFileNum = "000097" Then
tmpComm = pLoanAmt * 0.0065
ElseIf pFileNum = "000108" Then
tmpComm = pLoanAmt * 0.006
ElseIf pFileNum = "000060" Or pFileNum = "000058" Or pFileNum = "000110"
Then
' is pSumOfLoanAmt >= $3,000,000?
If pSumOfLoanAmt >= 3000000 Then
tmpComm = pLoanAmt * 0.006
' is pSumOfLoanAmt >= $1,000,000?
ElseIf pSumOfLoanAmt >= 1000000 Then
tmpComm = pLoanAmt * 0.0055
Else ' pSumOfLoanAmt < $1000000
tmpComm = pLoanAmt * 0.005
End If
End If
' return the commission
GetCommission = tmpComm
End Function
HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Frye said:
Thank you, this was quite helpful, but I do have another quesion regarding
using a function. I'm not sure what you mean by using a function. Could you
explaion this? If I can simplify my queries by doing something different,
that would be great.
:
:
I am trying to create an IIF statement in a query. This is the contents I
have, but continually receive an error. What do I need to change?
Commission: IIf([Accrual Monthly tbl].[File #]="000097",[Loan
Amount]*0.0065,IIf([Accrual Monthly tbl].[File #]="000108",[Loan
Amount]*0.006,IIf([Accrual Monthly tbl].[File #]="000060" or IIf([Accrual
Monthly tbl].[File #]="000058" or IIf([Accrual Monthly tbl].[File
#]="000110",IIf([sumofloan amount]>999999,[loan amount]*0.006,IIF([sumofloan
amount]<1000000,[loan amount]*0.005,IIf([sumofloan amount]>3000000,[loan
amount]*0.006,IIF([sumofloan amount]>999999 and [sumofloan
amount]<2999999,[loan amount]*0.0055,[Loan Amount]*.005)))))
I agree with Duane Hookom (after all, he IS a MVP... <grin> ... and I'm
not), a function is the way to go.
But I did look at your nested IIf() statements. I found 4 problems:
1) In the part that has
<snip>
IIf([Accrual Monthly tbl].[File #]="000060" or IIf([Accrual Monthly
tbl].[File #]="000058" or IIf([Accrual Monthly tbl].[File #]="000110",
<snip>
there are too many "IIf's" . It should be:
<snip>
IIf([Accrual Monthly tbl].[File #]="000060" or [Accrual Monthly
tbl].[File #]="000058" or [Accrual Monthly tbl].[File #]="000110",
<snip>
2) Not enough closing parentheses. The way you have it, there should be 6
closing parentheses.
3) Missing a "false" option for the ....IIF(... or ... or...,true,false)...
part.
4) The order you have the checks for the SumOfLoan Amount, the first two
take care of all possibilities, ie >999999 or <1000000. the 3 million and
the between 999999 and 2999999 will never be evaluated. So I re-did it:
greater than or equal to (>=)3000000 takes care of 3M+, then >= 1000000 takes
care of between 1M and 3M, any amount less than 1M defaults to [loan
amount]*0.005
Since I found problems, try this as a solution.
Commission: IIf([Accrual Monthly tbl].[File #]="000097",[Loan
Amount]*0.0065,IIf([Accrual Monthly tbl].[File #]="000108",[Loan
Amount]*0.006,IIf([Accrual Monthly tbl].[File #]="000060" Or [Accrual Monthly
tbl].[File #]="000058" Or [Accrual Monthly tbl].[File
#]="000110",IIf([sumofloan Amount]>=3000000,[loan
amount]*0.006,IIf([sumofloan Amount]>=1000000,[loan amount]*0.0055,[loan
amount]*0.005)),0)))
