How to define SQL?

[ccheng]

Hi,

Below, I have modified the calculation codes below as shown in blue, with the code I replaced in red.

        stSql2 = "SELECT  tc05_Services.Ix_Statut_Prestation_tc05,tc05_Services.Ix_Service_Modulebox, Sum(tc05_Services.CSUR_EB) AS SumOfCSUR_EB, Sum(tc05_Services.CSUR_MEP) AS SumOfCSUR_MEP, Sum(tc05_Services.CSUR_GPA) AS SumOfCSUR_GPA, Sum(tc05_Services.CSUR_GCL) AS SumOfCSUR_GCL, Sum(tc05_Services.CSUR_COM) AS SumOfCSUR_COM, Sum(tc05_Services.CSUR_REXP) AS SumOfCSUR_REXP"
        stSql2 = stSql2 & " FROM tc05_Services "
        stSql2 = stSql2 & " WHERE (((tc05_Services.Segment_tc05)='" & rs![Index_Segment] & "')) GROUP BY tc05_Services.Ix_Statut_Prestation_tc05, tc05_Services.Ix_Service_Modulebox;"
                   
        rs2.Open stSql2, con2, 1   ' 1 = adOpenKeyset
        While Not (rs2.EOF)
            Year = yearStartContract - 1
        'For each year, according to the duration of the contract
            For cpti = 1 To [COLOR=blue]10[/color blue] [COLOR=red]DurationContract[/color red]
                    exposant = cpti - 1
                    Annee = Annee + 1
                
                tableTt02.AddNew
                tableTt02("Index_Contrat") = IdContrat
                tableTt02("Segment_tc05") = rs![Index_Segment]
                tableTt02("statut_service") = rs2![Ix_Statut_Prestation_tc05]
                tableTt02("Ix_Service_Modulebox") = rs2![Ix_Service_Modulebox]
                tableTt02("Annee") = Annee
                tableTt02("CSR_EB") = 0
                tableTt02("CSR_MEP") = 0
                tableTt02("CSR_GPA") = Nz(rs2![SumOfCSUR_GPA]) * ((1 + InflationRate) ^ exposant)
                tableTt02("CSR_GCL") = (Nz(rs2![SumOfCSUR_GCL]))
                tableTt02("CSR_COM") = (Nz(rs2![SumOfCSUR_COM]))
                tableTt02("CSR_REXP") = Nz(rs2![SumOfCSUR_REXP]) * ((1 + InflationRate) ^ exposant)
           
              Next cpti
            rs2.MoveNext
        Wend
        rs2.Close
                           
        rs.MoveNext
    Wend
    
    tableTt02.Update
    
    rs.Close
    tableTt02.Close
    
    Set tableTt02 = Nothing
    Set rs = Nothing
    Set con = Nothing
    Set rs2 = Nothing
    Set con2 = Nothing

exit_sub:
    Exit Sub

Err_Sub:
    MsgBox (Err.Description)
    Resume exit_sub

End Sub

In changing the codes, I have now the sum of all the costs from Yr 2005 to Yr 2014. Given that the below-mentioned table "tt01_Detail_Recurrent" consists of the column "Year". How do I add another parametre in the codes below, so that it will only calculate the sum of costs for the duration of the contrat, e.g. 2005 to 2009?

    Call CalculRecurrentContrat(IdContrat)

    stSql = "SELECT Sum(tt01_Detail_Recurrent.CTR_tt01) AS SumOfCTR_tt01, Sum(tt01_Detail_Recurrent.CCR_tt01) AS SumOfCCR_tt01, Sum(tt01_Detail_Recurrent.CFR_tt01) AS SumOfCFR_tt01"
    stSql = stSql & " FROM tt01_Detail_Recurrent;"

    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
    If Not (rs.EOF) Then
            coutRecurrent = Nz(rs![SumOfCTR_tt01]) + Nz(rs![SumOfCCR_tt01]) + Nz(rs![SumOfCFR_tt01])
    End If
    rs.Close

Thank you in advance for your assistance!