Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

custom functions in Queries

Discussion in 'Information Technology' started by New TO Access, Oct 31, 2005.

  1. New TO Access

    New TO Access
    Expand Collapse
    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
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

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

    --
    Regards

    Jeff Boyce
    <Office/Access MVP>

    "New TO Access" <NewTOAccess@discussions.microsoft.com> wrote in message
    news:DE301AF7-B7CD-4235-9C5D-F59F689A0716@microsoft.com...
    > 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
    >
     
  4. '69 Camaro

    '69 Camaro
    Expand Collapse
    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.


    "New TO Access" wrote:

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

Share This Page