Uisng Public Function in Select Query...

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

Guest

I am attempting to use a Select Case statement in a public function within a
Select query to return the result of a formula to determine profitability.
Here is a sample of the code. It is not returning any results.

Public Function Gain()

Dim Investor As Variant
Dim OrigFee As Field
Dim DiscPts As Field
Dim InvYSP As Field
Dim LoanAmt As Field

Select Case Investor
Case "GMAC Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 35.5
Case "Countrywide Correspondent"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 6.5
Case "Washington Mutual Bank, FA"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 40
Case "Ohio Savings Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 190
Case "Option One Mortgage"
If [LoanAmt] > 130000 Then
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 200
Else
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 335
End If
End Select
End Function

I have tried using IIf however with the amount of "Investors", I need more
nesting ability.

Thanks for your help...
 
You would need to send the OrigFee, DiscPts, InvYSP, and Investor into the
function as arguments:

Public Function Gain(Investor as ..., OrigFee As Currency, DiscPts as
Currency,....) as Currency
' then don't Dim the values that come into the function as arguments

When you call the function in the query, use something like:

GainAmt: Gain([Investor], [OrigFee],...)

I would really question this solution since if you add another investor, you
will need to change your code. You should place your 35.5, 6.5,... values
into a table of investors so you could pass in this value.
 
It because the Investor has no value, I assume the Investor and the rest of
the values should pass from the query, in that case try this

For the function
Public Function Gain(Investor , OrigFee, DiscPts, InvYSP, LoanAmt)

Select Case Investor
Case "GMAC Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 35.5
Case "Countrywide Correspondent"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 6.5
Case "Washington Mutual Bank, FA"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 40
Case "Ohio Savings Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 190
Case "Option One Mortgage"
If [LoanAmt] > 130000 Then
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 200
Else
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 335
End If
End Select
End Function
==============================
The SQL for the query should be

Select Gain([Investor] , [OrigFee], [DiscPts], [InvYSP], [LoanAmt]) as
NewGain From TableName
 
Duane,

The table approach works so much better for me. I can also allow a user to
added investors/income amts to the table thus eleviating IT involvement. As
always, you guys come through. Thanks.

Duane Hookom said:
You would need to send the OrigFee, DiscPts, InvYSP, and Investor into the
function as arguments:

Public Function Gain(Investor as ..., OrigFee As Currency, DiscPts as
Currency,....) as Currency
' then don't Dim the values that come into the function as arguments

When you call the function in the query, use something like:

GainAmt: Gain([Investor], [OrigFee],...)

I would really question this solution since if you add another investor, you
will need to change your code. You should place your 35.5, 6.5,... values
into a table of investors so you could pass in this value.

--
Duane Hookom
MS Access MVP
--

Ken D. said:
I am attempting to use a Select Case statement in a public function within
a
Select query to return the result of a formula to determine profitability.
Here is a sample of the code. It is not returning any results.

Public Function Gain()

Dim Investor As Variant
Dim OrigFee As Field
Dim DiscPts As Field
Dim InvYSP As Field
Dim LoanAmt As Field

Select Case Investor
Case "GMAC Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 35.5
Case "Countrywide Correspondent"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 6.5
Case "Washington Mutual Bank, FA"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 40
Case "Ohio Savings Bank"
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 190
Case "Option One Mortgage"
If [LoanAmt] > 130000 Then
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 200
Else
Gain = [OrigFee] + [DiscPts] + [InvYSP] + 335
End If
End Select
End Function

I have tried using IIf however with the amount of "Investors", I need more
nesting ability.

Thanks for your help...
Click to expand...
Click to expand...
 
Back
Top