custom functions in Queries

[Guest]

I have a custom function that I use on an OPenForm action to calculate and
display some information.

The function is:

Private Function Calculate_Charges()
Call Lookup_Charges

PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType]")
ICLRateNow = DLookup("[ICL]", "[TblCharges]", "[Class] = [PolType]")
GSTRateNow = DLookup("[GST]", "[TblCharges]", "[Class] = [PolType]")

If Me!PayFreq = "Annual" And Me!InstNo = 1 Then
Me!SD.Value = (Me!Premium * SdRateNow) / 100
Me!ICL.Value = (Me!Premium * ICLRateNow) / 100
Me!GST.Value = ((Me!Premium + Me!SD - Me!Brokerage) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100

ElseIf Me!PayFreq = "Quarterly" And Me!InstNo = 1 Then
Me!SD.Value = ((Me!Premium * 4) * SdRateNow) / 100
Me!ICL.Value = ((Me!Premium * 4) * ICLRateNow) / 100
Me!GST.Value = (((Me!Premium * 4) + Me!SD - Me!Brokerage) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100

Else

Me!SD.Value = 0
Me!ICL.Value = 0
Me!GST.Value = 0
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End If
End Function


I need to use exactly the same calculation in a query. I have tried nesting
IIf statements but it seems too cumbersome.

Is there a way I can "call" this function in my query?

Thanks

David

 

[Guest]

Hi, David.

Is there a way I can "call" this function in my query?

If you fix the problems, you could call it, but it will only return a single
value per row, not the five values that are calculated in your function. So,
you'd need to create three custom public functions in a standard module and
create a query to call those functions. The query would look something like
this (warning: air code):

SELECT DLOOKUP ("SD", "TblCharges", "Class = " & PolType) AS SdRateNow,
DLOOKUP ("ICL", "TblCharges", "Class = " & PolType) AS ICLRateNow,
DLOOKUP ("GST", "TblCharges", "Class = " & PolType) AS GSTRateNow,
getSD(PayFreq, InstNo, Premium, SdRateNow) AS SD,
getICL(PayFreq, InstNo, Premium, ICLRateNow) AS ICL,
getGST(PayFreq, InstNo, Premium, SD, Brokerage, GSTRateNow) AS GST,
(BrokerFee * GSTRateNow / 100) AS GSTFee,
(Brokerage * GSTRateNow / 100) AS GSTBrokerage
FROM tblMyTable;

.. . . where tblMyTable is the name of the table (or query). This table (or
query)must contain the following fields: PolType, PayFreq, InstNo, Premium,
Brokerage, and BrokerFee.

The three functions would look something like this (warning: air code):

Public Function getSD(sPayFreq As String, InstNo As Long, Premium As
Currency, SdRateNow As Double) As Double
If sPayFreq = "Annual" And InstNo = 1 Then
getSD = (Premium * SdRateNow) / 100
ElseIf sPayFreq = "Quarterly" And InstNo = 1 Then
getSD = ((Premium * 4) * SdRateNow) / 100
Else
getSD = 0
End If
Exit Function

Public Function getICL(sPayFreq As String, InstNo As Long, Premium As
Currency, ICLRateNow As Double) As Double
If sPayFreq = "Annual" And InstNo = 1 Then
getICL = (Premium * ICLRateNow / 100
ElseIf sPayFreq = "Quarterly" And InstNo = 1 Then
getICL = ((Premium * 4) * ICLRateNow) / 100
Else
getICL = 0
End If
Exit Function

Public Function getGST(sPayFreq As String, InstNo As Long, Premium As
Currency, SD AS Double, Brokerage AS Double, GSTRateNow As Double) As Double
If sPayFreq = "Annual" And InstNo = 1 Then
getGST = ((Premium + SD - Brokerage) * GSTRateNow) / 100
ElseIf sPayFreq = "Quarterly" And InstNo = 1 Then
getGST = (((Premium * 4) + SD - Brokerage) * GSTRateNow) / 100
Else
getGST = 0
End If
Exit Function

I'm guessing at your field names and data types, but this code should get
you started.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a custom function that I use on an OPenForm action to calculate and
display some information.

The function is:

Private Function Calculate_Charges()
Call Lookup_Charges

PolType = Me!PolType
SdRateNow = DLookup("[SD]", "[TblCharges]", "[Class] = [PolType]")
ICLRateNow = DLookup("[ICL]", "[TblCharges]", "[Class] = [PolType]")
GSTRateNow = DLookup("[GST]", "[TblCharges]", "[Class] = [PolType]")

If Me!PayFreq = "Annual" And Me!InstNo = 1 Then
Me!SD.Value = (Me!Premium * SdRateNow) / 100
Me!ICL.Value = (Me!Premium * ICLRateNow) / 100
Me!GST.Value = ((Me!Premium + Me!SD - Me!Brokerage) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100

ElseIf Me!PayFreq = "Quarterly" And Me!InstNo = 1 Then
Me!SD.Value = ((Me!Premium * 4) * SdRateNow) / 100
Me!ICL.Value = ((Me!Premium * 4) * ICLRateNow) / 100
Me!GST.Value = (((Me!Premium * 4) + Me!SD - Me!Brokerage) * GSTRateNow) / 100
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100

Else

Me!SD.Value = 0
Me!ICL.Value = 0
Me!GST.Value = 0
Me!GSTFee.Value = ((Me!BrokerFee) * GSTRateNow) / 100
Me!GSTBrokerage.Value = (Me!Brokerage * GSTRateNow) / 100
End If
End Function


I need to use exactly the same calculation in a query. I have tried nesting
IIf statements but it seems too cumbersome.

Is there a way I can "call" this function in my query?

Thanks

David

 

[Jeff Boyce]

Have you tried inserting the function as a "field" in the query design grid?