By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

How to reslove ODBC 3151: Error

syedshaffee
P: 91
hey people ,

I'm stuck some where, whenever i am generating a report form my front-end I'm getting an error "ODBC 3151 ERROR"
I was assuming that their might be a problem in query
but when I'm executing on sql it is giving me the output for reference I'm giving the u people the code for the same
Expand|Select|Wrap|Line Numbers
  1. Private Function FillTempTable(strFilterDesc As String) As Boolean
  2.     Dim rstcontract As DAO.Recordset
  3.     Dim rst As DAO.Recordset
  4.     Dim rst1 As DAO.Recordset
  5.  
  6.     Dim rstInvoice As DAO.Recordset
  7.     Dim strUpdate As String
  8.     Dim strcontractno As String
  9.     Dim strInvoiceDetail As String
  10.     Dim strInvoiceID As String
  11.     Dim strInvoiceNo As String
  12.     Dim strInvoiceDate As String
  13.     Dim strInvGeneratedDate As String
  14.     Dim strInvoiceAmount As String
  15.     Dim strInvoiceType As String
  16.  
  17.     Dim strSQl As String
  18.     Dim strAllocationAmount As String
  19.  
  20.     Dim intCnt As Long
  21.     Dim intTotalRecords As Integer
  22.  
  23.     Dim strRewriteContractNo As String
  24.     Dim strRVRecovery As String
  25.     Dim strRVTransfer As String
  26.  
  27.     Dim dblContractRewrite As Double
  28.  
  29.     Dim strContractStatus As Boolean
  30.  
  31.     Dim blnGroup1 As Boolean
  32.  
  33.     Dim TotalPVofRV As Double
  34.     Dim TotalNewPVofRV As Double
  35.     Dim TotalOldPVofRV As Double
  36.     Dim dblBalanceRV As Double
  37.  
  38.     Dim strContractVolume As String
  39.     Dim strNominalRV As String
  40.     Dim strGRV As String
  41.  
  42.     Dim strInvDueFromDate As String
  43.     Dim strInvDueToDate As String
  44.  
  45.     Dim strGeneratedFromDate As String
  46.     Dim strGeneratedToDate As String
  47.  
  48.     Dim strSQLConditon As String
  49.     Dim strCashFlowTypes As String
  50.  
  51.     Dim strContractFrequency As String
  52.  
  53.     Dim strPVofRV_BF As String
  54.     Dim strPVOfRV As String
  55.  
  56.     Dim strRemainingRVAsonDate As String
  57.  
  58.     Dim strAccoundingDate As String
  59.  
  60.     Dim strinvoiceRecovery As String
  61.  
  62.     Dim strBillingPeriod As String
  63.  
  64.     Dim strCustomerName As String
  65.     Dim strADCustomerName As String
  66.     Dim strAccountManager As String
  67.  
  68.     On Error GoTo Err_FillTempTable
  69.  
  70.     db.Execute "DELETE FROM ztSecondaryBilling ", dbSeeChanges
  71.  
  72.     strInvDueFromDate = ""
  73.     strInvDueToDate = ""
  74.  
  75.     strGeneratedFromDate = ""
  76.     strGeneratedToDate = ""
  77.  
  78.     strSQLConditon = ""
  79.     strCashFlowTypes = ""
  80.  
  81.     strcontractno = ""
  82.  
  83.     If Me.chkDueDate = True Then
  84.         If Trim(Nz(Me!txtInvDueFromDate, "")) <> "" Then
  85.             strInvDueFromDate = Format(Trim(Nz(Me!txtInvDueFromDate, "")), "dd-mmm-yyyy")
  86.         End If
  87.         If Trim(Nz(Me!txtInvDueToDate, "")) <> "" Then
  88.             strInvDueToDate = Format(Trim(Nz(Me!txtInvDueToDate, "")), "dd-mmm-yyyy")
  89.         End If
  90.     End If
  91.  
  92.     If Me.chkGenDate = True Then
  93.         If Trim(Nz(Me!txtGenFromDate, "")) <> "" Then
  94.             strGeneratedFromDate = Format(Trim(Nz(Me!txtGenFromDate, "")), "dd-mmm-yyyy")
  95.         End If
  96.         If Trim(Nz(Me!txtGenToDate, "")) <> "" Then
  97.             strGeneratedToDate = Format(Trim(Nz(Me!txtGenToDate, "")), "dd-mmm-yyyy")
  98.         End If
  99.     End If
  100.  
  101.  
  102.     ProgressBar.Visible = True
  103.     txtProgerssBar.Visible = True
  104.     lblImportCaption.Visible = True
  105.  
  106.     ProgressBar.Value = 0
  107.     txtProgerssBar = "0%"
  108.     lblImportCaption.Caption = "Collecting Data ..."
  109.  
  110.     'strFilterDesc = " (Contract_Detail.Contract_Bank_Settlement>0) "
  111.  
  112.     strFilterDesc = strFilterDesc & " AND (Contract_Detail.Contract_Expiry_Date BETWEEN #01-Apr-2009# AND #31-Mar-2011#) "
  113.  
  114.     intCnt = 0
  115.     intTotalRecords = 0
  116.     Set rst = Nothing
  117.     If Trim(strFilterDesc) <> "" Then strFilterDesc = " WHERE " & strFilterDesc
  118.     Set rst = db.OpenRecordset("SELECT DISTINCT Count(Contract_Detail.Contract_No) AS TotalContracts FROM Contract_Detail " & strFilterDesc & " ", dbOpenDynaset, dbSeeChanges)
  119.     If Not rst.EOF Then
  120.         intTotalRecords = val(Nz(rst!TotalContracts, 0))
  121.     End If
  122.     rst.Close
  123.     Set rst = Nothing
  124.  
  125.     blnGroup1 = False
  126.     dblBalanceRV = 0
  127.     TotalPVofRV = 0
  128.     TotalOldPVofRV = 0
  129.     Set rstcontract = Nothing
  130.     Set rstcontract = db.OpenRecordset("SELECT * FROM Contract_Detail " & strFilterDesc & " ORDER BY Contract_Detail.Contract_No ASC ", dbOpenDynaset, dbSeeChanges)
  131.     If Not rstcontract.EOF Then
  132.         rstcontract.MoveFirst
  133.         ProgressBar.MAX = val(intTotalRecords)
  134.         While Not rstcontract.EOF
  135.             Set rstInvoice = Nothing
  136.             blnGroup1 = False
  137.  
  138.             strPVofRV_BF = ""
  139.             TotalPVofRV = 0
  140.             TotalOldPVofRV = 0
  141.             strSQLConditon = ""
  142.             strSQl = ""
  143.             strNominalRV = ""
  144.             strGRV = ""
  145.             dblContractRewrite = 0
  146.             strRemainingRVAsonDate = "0"
  147.             strBillingPeriod = ""
  148.  
  149.             intCnt = val(intCnt) + 1
  150.  
  151.             strcontractno = Trim(Nz(rstcontract!Contract_no, ""))
  152.  
  153.             dblContractRewrite = val(Nz(rstcontract!Contract_Rewrite, 0))
  154.  
  155.             'dblBalanceRV = val(Nz(rstContract!ContractRemainingRV, 0))
  156.  
  157.             strCustomerName = RetrieveName("Customer_Detail", "Customer_ID", "Customer_Name", val(Nz(rstcontract!Contract_customer_id, 0)))
  158.  
  159.             If dblContractRewrite <> 0 Then
  160.                 strContractVolume = val(Nz(rstcontract!Contract_Asset_Value, 0)) + val(Nz(rstcontract!ContractRewrittenAssetValue, 0))
  161.                 strNominalRV = val(Nz(rstcontract!Contract_rv_calculation, 0)) + val(Nz(rstcontract!Contract_old_rv_calculation, 0)) + val(Nz(rstcontract!RevisedNominalRV, 0))
  162.  
  163.                 If Not IsNull(rstcontract!BlueSheetPVResidual) Then
  164.                     TotalPVofRV = val(Nz(rstcontract!BlueSheetPVResidual, 0))
  165.                 End If
  166.                 TotalOldPVofRV = 0
  167.                 If val(Nz(rstcontract!Contract_Rewrite, 0)) <> 0 Then
  168.                     If val(Nz(rstcontract!RevisedRVPercent, 0)) <> 0 Then
  169.                         TotalOldPVofRV = val(TotalOldPVofRV) + val(Nz(rstcontract!RevisedPVOfRV, 0))
  170.                     End If
  171.                     If val(Nz(rstcontract!ContractPVRVOldEquip, 0)) <> 0 Then
  172.                         TotalOldPVofRV = val(TotalOldPVofRV) + val(Nz(rstcontract!ContractPVRVOldEquip, 0))
  173.                     End If
  174.                 End If
  175.                 dblBalanceRV = TotalPVofRV + TotalOldPVofRV
  176.                 strPVOfRV = val(TotalPVofRV) + val(TotalOldPVofRV)
  177.                 'strGRV = FV(val(gGRVPercentage) / 1200, (val(Nz(rstcontract!Contract_term, 0)) + IIf(val(Nz(rstcontract!Contract_perdiem, 0)) = 0, 0, IIf(val(Nz(rstcontract!Contract_perdiem, 0)) <= 30, 1, IIf(val(Nz(rstcontract!Contract_perdiem, 0)) <= 60, 2, 3)))), 0, -1 * val(TotalPVofRV))
  178.                 strGRV = val(Nz(rstcontract!BlueSheetRVCOF, 0))
  179.             Else
  180.                 strContractVolume = val(Nz(rstcontract!Contract_Asset_Value, 0))
  181.                 strNominalRV = val(Nz(rstcontract!Contract_rv_calculation, 0))
  182.  
  183.                 TotalPVofRV = val(Nz(rstcontract!BlueSheetPVResidual, 0))
  184.                 TotalOldPVofRV = 0
  185.                 dblBalanceRV = TotalPVofRV
  186.                 strPVOfRV = val(TotalPVofRV) + val(TotalOldPVofRV)
  187.                 strGRV = val(Nz(rstcontract!BlueSheetRVCOF, 0))
  188.             End If
  189.  
  190.             If Trim(strInvDueFromDate) <> "" Then
  191.                 strPVofRV_BF = GetRemainingRV(strcontractno, DateAdd("d", -1, strInvDueFromDate), False)
  192.             ElseIf Trim(strGeneratedFromDate) <> "" Then
  193.                 strPVofRV_BF = GetRemainingRV(strcontractno, DateAdd("d", -1, Trim(Nz(Me!txtGenFromDate, ""))), True)
  194.             Else
  195.                 If dblContractRewrite <> 0 Then
  196.                     strPVofRV_BF = TotalPVofRV + TotalOldPVofRV
  197.                 Else
  198.                     strPVofRV_BF = TotalPVofRV + TotalOldPVofRV
  199.                 End If
  200.             End If
  201.  
  202.             If Trim(strInvDueToDate) <> "" Then
  203.                 strRemainingRVAsonDate = GetRemainingRV(strcontractno, CStr(Format(CDate(strInvDueToDate), "dd-mmm-yyyy")), False)
  204.             ElseIf Trim(strGeneratedToDate) <> "" Then
  205.                 strRemainingRVAsonDate = GetRemainingRV(strcontractno, CStr(Format(CDate(strGeneratedToDate), "dd-mmm-yyyy")), True)
  206.             Else
  207.                 strRemainingRVAsonDate = GetRemainingRV(strcontractno, "", False)
  208.             End If
  209.  
  210.  
  211.             strAccountManager = ""
  212.  
  213.             Set rst = db.OpenRecordset("SELECT Customer_detail.Customer_id, Customer_detail.Customer_support_id, Staff.Staff_id, Staff.Staff_name FROM Customer_detail LEFT OUTER JOIN Staff ON Customer_detail.Customer_support_id = Staff.Staff_id WHERE (Customer_detail.Customer_id = " & val(Nz(rstcontract!Contract_customer_id, 0)) & ") ", dbOpenDynaset, dbSeeChanges)
  214.             If Not rst.EOF Then
  215.                 If Not IsNull(rst!Staff_name) Then
  216.                     If Trim(Nz(rst!Staff_name, "")) <> "" Then
  217.                         strAccountManager = Trim(Nz(rst!Staff_name, ""))
  218.                     End If
  219.                 End If
  220.             End If
  221.  
  222.  
  223.             strAccoundingDate = ""
  224.             strSQLConditon = ""
  225.             Set rstInvoice = Nothing
  226.  
  227.             If chkRewrite Then
  228.                 strSQl = " SELECT DISTINCTROW RewriteRVAllocation.ContractNo, RewriteRVAllocation.RVRecovery, RewriteRVAllocation.RVTransfer, RewriteRVAllocation.RewriteContractNo "
  229.                 strSQl = strSQl & " FROM RewriteRVAllocation "
  230.                 strSQl = strSQl & " WHERE RewriteRVAllocation.ContractNo='" & strcontractno & "' "
  231.                 Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  232.                 If Not rstInvoice.EOF Then
  233.                     rstInvoice.MoveFirst
  234.                     While Not rstInvoice.EOF
  235.                         strRewriteContractNo = "": strRVRecovery = "": strRVTransfer = ""
  236.                         strinvoiceRecovery = "": strADCustomerName = "": strInvoiceType = ""
  237.  
  238.                         strRewriteContractNo = CStr(Nz(rstInvoice!RewriteContractNo, ""))
  239.                         If Trim(strRewriteContractNo) <> "" Then
  240.                             Set rst1 = db.OpenRecordset("SELECT * FROM Contract_Detail WHERE Contract_No = '" & strRewriteContractNo & "' ", dbOpenDynaset, dbSeeChanges)
  241.                             If Not rst1.EOF Then
  242.                                 strInvoiceDate = CStr(Nz(rst1!Contract_Accounting_Date, ""))
  243.                                 If Trim(strInvoiceDate) <> "" Then strInvoiceDate = Format(CDate(strInvoiceDate), "dd-mmm-yyyy")
  244.  
  245.                                 strInvGeneratedDate = CStr(Nz(rst1!Contract_Accounting_Date, ""))
  246.                                 If Trim(strInvGeneratedDate) <> "" Then strInvGeneratedDate = Format(CDate(strInvGeneratedDate), "dd-mmm-yyyy")
  247.                             End If
  248.                         End If
  249.  
  250.                         strRVRecovery = val(Nz(rstInvoice!RVRecovery, 0))
  251.                         strRVTransfer = val(Nz(rstInvoice!RVTransfer, 0))
  252.  
  253.                         strInvoiceNo = strRewriteContractNo
  254.  
  255.                         strInvoiceAmount = val(strRVRecovery)
  256.                         strinvoiceRecovery = val(strRVRecovery)
  257.  
  258.                         strInvoiceType = "Rewrite"
  259.  
  260.                         strADCustomerName = ""
  261.  
  262.                         strUpdate = ""
  263.                         strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  264.                         strUpdate = strUpdate & " ( "
  265.                         strUpdate = strUpdate & " CustomerName " & " "
  266.                         strUpdate = strUpdate & " , ContractNo " & " "
  267.                         strUpdate = strUpdate & " , AccountManager " & " "
  268.                         strUpdate = strUpdate & " , InvoiceNo " & " "
  269.                         strUpdate = strUpdate & " , InvoiceDate " & " "
  270.                         strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  271.                         strUpdate = strUpdate & " , InvocieAmount " & " "
  272.                         strUpdate = strUpdate & " , NominalRV " & " "
  273.                         strUpdate = strUpdate & " , PVOfRV " & " "
  274.                         strUpdate = strUpdate & " , GRV " & " "
  275.                         strUpdate = strUpdate & " , InvoiceRecovery " & " "
  276.                         strUpdate = strUpdate & " , InvoiceType " & " "
  277.                         strUpdate = strUpdate & " , ADCustomerName " & " "
  278.                         strUpdate = strUpdate & " ) VALUES ( "
  279.  
  280.                         strUpdate = strUpdate & " '" & strCustomerName & "' "
  281.                         strUpdate = strUpdate & " , '" & strcontractno & "' "
  282.                         strUpdate = strUpdate & " , '" & strAccountManager & "' "
  283.                         strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  284.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  285.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  286.                         strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  287.                         If blnGroup1 = False Then
  288.                             strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  289.                             strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  290.                             strUpdate = strUpdate & " , " & val(strGRV) & " "
  291.                             blnGroup1 = True
  292.                         Else
  293.                             strUpdate = strUpdate & " , 0 "
  294.                             strUpdate = strUpdate & " , 0 "
  295.                             strUpdate = strUpdate & " , 0 "
  296.                         End If
  297.                         strUpdate = strUpdate & " , " & val(strinvoiceRecovery) & " "
  298.                         strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  299.                         strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  300.  
  301.                         strUpdate = strUpdate & " ) "
  302.  
  303.                         If Trim(strUpdate) <> "" Then
  304.                             Debug.Print strUpdate
  305.                             db.Execute strUpdate, dbSeeChanges
  306.                         End If
  307.  
  308.                         rstInvoice.MoveNext
  309.                     Wend
  310.                 End If
  311.             End If
  312.  
  313.  
  314.             ''''' Inertia Invoice
  315.             If chkInertia Then
  316.                 If CBool(rstcontract!ShortTermContract) = False Then
  317.                     strSQLConditon = ""
  318.                     If Trim(strInvDueFromDate) <> "" Then
  319.                         If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  320.                         strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  321.                     End If
  322.                     If Trim(strInvDueToDate) <> "" Then
  323.                         If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  324.                         strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  325.                     End If
  326.                     If Trim(strGeneratedFromDate) <> "" Then
  327.                         If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  328.                         strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  329.                     End If
  330.                     If Trim(strGeneratedToDate) <> "" Then
  331.                         If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  332.                         strSQLConditon = strSQLConditon & " Contract_CashFlow.CashFlowGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  333.                     End If
  334.  
  335.                     If Trim(strSQLConditon) <> "" Then
  336.                         strSQl = " SELECT Contract_cashflow.*, CashflowType.CashflowTypeDesc, ContractInvoice.InvoiceRVRecovery "
  337.                         strSQl = strSQl & " FROM (Contract_cashflow INNER JOIN CashflowType ON Contract_cashflow.CashflowTypeID = CashflowType.CashflowTypeID) LEFT JOIN ContractInvoice ON (Contract_cashflow.CashflowNumber = ContractInvoice.CashFlowNumber) AND (Contract_cashflow.CashflowContractNo = ContractInvoice.InvoiceContractNo) "
  338.                         strSQl = strSQl & " WHERE Contract_cashflow.CashFlowContractNo='" & strcontractno & "' "
  339.                         strSQl = strSQl & " AND Contract_cashflow.CashFlowTypeID IN(1) AND " & strSQLConditon & " "
  340.                     Else
  341.                         strSQl = " SELECT Contract_cashflow.*, CashflowType.CashflowTypeDesc, ContractInvoice.InvoiceRVRecovery "
  342.                         strSQl = strSQl & " FROM (Contract_cashflow INNER JOIN CashflowType ON Contract_cashflow.CashflowTypeID = CashflowType.CashflowTypeID) LEFT JOIN ContractInvoice ON (Contract_cashflow.CashflowNumber = ContractInvoice.CashFlowNumber) AND (Contract_cashflow.CashflowContractNo = ContractInvoice.InvoiceContractNo) "
  343.                         strSQl = strSQl & " WHERE Contract_cashflow.CashFlowContractNo='" & strcontractno & "' "
  344.                         strSQl = strSQl & " AND Contract_cashflow.CashFlowTypeID IN(1) "
  345.                     End If
  346.                     Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  347.                     If Not rstInvoice.EOF Then
  348.                         rstInvoice.MoveFirst
  349.                         While Not rstInvoice.EOF
  350.                             strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
  351.                             strBillingPeriod = ""
  352.  
  353.                             If Not IsNull(rstInvoice!CashflowNumber) Then
  354.                                 If Trim(rstInvoice!CashflowNumber) <> "" Then
  355.                                     strInvoiceID = CStr(Trim(rstInvoice!CashflowNumber))
  356.                                 End If
  357.                             End If
  358.                             If Not IsNull(rstInvoice!CashflowDate) Then
  359.                                 If Trim(rstInvoice!CashflowDate) <> "" Then
  360.                                     strInvoiceNo = Right(Year(Trim(rstInvoice!CashflowDate)), 2) & String(2 - Len(Month(Trim(rstInvoice!CashflowDate))), "0") & Month(Trim(rstInvoice!CashflowDate)) & "-" & Trim(strcontractno)
  361.                                 End If
  362.                             End If
  363.                             If Not IsNull(rstInvoice!CashflowDate) Then
  364.                                 If Trim(rstInvoice!CashflowDate) <> "" Then
  365.                                     strInvoiceDate = CStr(Trim(rstInvoice!CashflowDate))
  366.                                 End If
  367.                             End If
  368.                             strBillingPeriod = ""
  369.                             Set rst = db.OpenRecordset("SELECT Contract_Frequency FROM Contract_Detail WHERE Contract_No = '" & Nz(strcontractno, "") & "' ", dbOpenDynaset, dbSeeChanges)
  370.                             If Not rst.EOF Then
  371.                                 strContractFrequency = Nz(rst!Contract_frequency, 0)
  372.                                 If Trim(strInvoiceDate) <> "" And val(strContractFrequency) > 0 Then
  373.                                     strBillingPeriod = Format(CDate(strInvoiceDate), "dd-mmm-yyyy") & " to " & Format(DateAdd("d", -1, CDate(Format(DateAdd("m", Round(val(strContractFrequency) / 30), CDate(strInvoiceDate)), "dd-mmm-yyyy"))), "dd-mmm-yyyy")
  374.                                 Else
  375.                                     strBillingPeriod = ""
  376.                                 End If
  377.                             End If
  378.                             rst.Close
  379.  
  380.                             If Not IsNull(rstInvoice!CashFlowGeneratedDate) Then
  381.                                 If Trim(rstInvoice!CashFlowGeneratedDate) <> "" Then
  382.                                     strInvGeneratedDate = CStr(Trim(Nz(rstInvoice!CashFlowGeneratedDate, "")))
  383.                                 End If
  384.                             End If
  385.                             If Not IsNull(rstInvoice!CashflowAmount) Then
  386.                                 If Trim(rstInvoice!CashflowAmount) <> "" Then
  387.                                     strInvoiceAmount = val(Nz(rstInvoice!CashflowAmount, 0)) + val(Nz(rstInvoice!CashFlowServiceRental, 0))
  388.                                 End If
  389.                             End If
  390.                             If Not IsNull(rstInvoice!InvoiceRVRecovery) Then
  391.                                 If Trim(rstInvoice!InvoiceRVRecovery) <> "" Then
  392.                                     strAllocationAmount = CStr(Trim(rstInvoice!InvoiceRVRecovery))
  393.                                 End If
  394.                             End If
  395.     ''                        If Not IsNull(rstInvoice!ContractStatusDesc) Then
  396.     ''                            If Trim(rstInvoice!ContractStatusDesc) <> "" Then
  397.     ''                                strInvoiceType = CStr(Trim("Inertia"))
  398.     ''                            End If
  399.     ''                        End If
  400.                             strInvoiceType = CStr(Trim("Inertia"))
  401.  
  402.                             Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE ContractNo = '" & strcontractno & "' AND InvoiceID=" & strInvoiceID & " AND InvoiceNo = '" & strInvoiceNo & "' ", dbOpenDynaset, dbSeeChanges)
  403.                             If Not rst.EOF Then
  404.                                 strInvoiceAmount = 0
  405.                             End If
  406.                             rst.Close
  407.  
  408.                             strUpdate = ""
  409.                             strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  410.                             strUpdate = strUpdate & " ( "
  411.                             strUpdate = strUpdate & " CustomerName " & " "
  412.                             strUpdate = strUpdate & " , ContractNo " & " "
  413.                             strUpdate = strUpdate & " , AccountManager " & " "
  414.                             strUpdate = strUpdate & " , InvoiceID " & " "
  415.                             strUpdate = strUpdate & " , InvoiceNo " & " "
  416.                             strUpdate = strUpdate & " , InvoiceDate " & " "
  417.                             If Trim(strInvGeneratedDate) <> "" Then
  418.                                 strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  419.                             End If
  420.                             strUpdate = strUpdate & " , InvocieAmount " & " "
  421.                             strUpdate = strUpdate & " , NominalRV " & " "
  422.                             strUpdate = strUpdate & " , PVOfRV " & " "
  423.                             strUpdate = strUpdate & " , GRV " & " "
  424.                             strUpdate = strUpdate & " , InvoiceRecovery " & " "
  425.                             strUpdate = strUpdate & " , InvoiceType " & " "
  426.                             strUpdate = strUpdate & " , ADCustomerName " & " "
  427.                             strUpdate = strUpdate & " ) VALUES ( "
  428.  
  429.                             strUpdate = strUpdate & " '" & strCustomerName & "' "
  430.                             strUpdate = strUpdate & " , '" & strcontractno & "' "
  431.                             strUpdate = strUpdate & " , '" & strAccountManager & "' "
  432.                             strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
  433.                             strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  434.                             strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  435.                             If Trim(strInvGeneratedDate) <> "" Then
  436.                                 strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  437.                             End If
  438.                             strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  439.                             If blnGroup1 = False Then
  440.                                 strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  441.                                 strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  442.                                 strUpdate = strUpdate & " , " & val(strGRV) & " "
  443.                                 blnGroup1 = True
  444.                             Else
  445.                                 strUpdate = strUpdate & " , 0 "
  446.                                 strUpdate = strUpdate & " , 0 "
  447.                                 strUpdate = strUpdate & " , 0 "
  448.                             End If
  449.                             strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
  450.                             strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  451.                             strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  452.  
  453.                             strUpdate = strUpdate & " ) "
  454.  
  455.                             If Trim(strUpdate) <> "" Then
  456.                                 Debug.Print strUpdate
  457.                                 db.Execute strUpdate, dbSeeChanges
  458.                             End If
  459.  
  460.                             rstInvoice.MoveNext
  461.                         Wend
  462.                     End If
  463.                 End If
  464.             End If
  465.  
  466.             '''''       Sale Invoice
  467.             If chkSale Then
  468.                 strSQLConditon = ""
  469.                 If Trim(strInvDueFromDate) <> "" Then
  470.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  471.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  472.                 End If
  473.                 If Trim(strInvDueToDate) <> "" Then
  474.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  475.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  476.                 End If
  477.                 If Trim(strGeneratedFromDate) <> "" Then
  478.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  479.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  480.                 End If
  481.                 If Trim(strGeneratedToDate) <> "" Then
  482.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  483.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  484.                 End If
  485.  
  486.                 strSQl = ""
  487.                 If Trim(strSQLConditon) <> "" Then
  488.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  489.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  490.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  491.                     strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
  492.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  493.                     strSQl = strSQl & " Union "
  494.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  495.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  496.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  497.                     strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
  498.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  499.                 Else
  500.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  501.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  502.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  503.                     strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
  504.                     strSQl = strSQl & " Union "
  505.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  506.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  507.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  508.                     strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=1)) "
  509.                 End If
  510.  
  511.                 Set rstInvoice = Nothing
  512.     '            strSQL = "SELECT Invoice.* FROM Invoice WHERE ADInvoice.Invoice_contract_no='" & strContractNo & "' "
  513.                 Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  514.                 If Not rstInvoice.EOF Then
  515.                     rstInvoice.MoveFirst
  516.                     While Not rstInvoice.EOF
  517.                         strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
  518.                         strBillingPeriod = ""
  519.                         If Not IsNull(rstInvoice!InvoiceID) Then
  520.                             If Trim(rstInvoice!InvoiceID) <> "" Then
  521.                                 strInvoiceID = CStr(Trim(rstInvoice!InvoiceID))
  522.                             End If
  523.                         End If
  524.                         If Not IsNull(rstInvoice!InvoiceNo) Then
  525.                             If Trim(rstInvoice!InvoiceNo) <> "" Then
  526.                                 strInvoiceNo = CStr(Trim(rstInvoice!InvoiceNo))
  527.                             End If
  528.                         End If
  529.                         If Not IsNull(rstInvoice!InvoiceDate) Then
  530.                             If Trim(rstInvoice!InvoiceDate) <> "" Then
  531.                                 strInvoiceDate = CStr(Trim(rstInvoice!InvoiceDate))
  532.                             End If
  533.                         End If
  534.                         If Not IsNull(rstInvoice!InvoiceGeneratedDate) Then
  535.                             If Trim(rstInvoice!InvoiceGeneratedDate) <> "" Then
  536.                                 strInvGeneratedDate = CStr(Trim(rstInvoice!InvoiceGeneratedDate))
  537.                             End If
  538.                         End If
  539.                         If Not IsNull(rstInvoice!InvoiceTotal) Then
  540.                             If Trim(rstInvoice!InvoiceTotal) <> "" Then
  541.                                 strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceTotal))
  542.                             End If
  543.                         End If
  544.                         If Not IsNull(rstInvoice!ADRVAllocation) Then
  545.                             If Trim(rstInvoice!ADRVAllocation) <> "" Then
  546.                                 strAllocationAmount = CStr(Trim(rstInvoice!ADRVAllocation))
  547.                             End If
  548.                         End If
  549.                         If Not IsNull(rstInvoice!InvoiceTypeID) Then
  550.                             If val(Nz(rstInvoice!InvoiceTypeID, 0)) = 1 Then
  551.                                 strInvoiceType = CStr(Trim("Sales Invoice"))
  552.                             ElseIf val(Nz(rstInvoice!InvoiceTypeID, 0)) = 2 Then
  553.                                 strInvoiceType = CStr(Trim("Repair Invoice"))
  554.                             End If
  555.                         Else
  556.                             strInvoiceType = CStr(Trim("Sales Invoice"))
  557.                         End If
  558.  
  559.                         Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
  560.                         If Not rst.EOF Then
  561.                             strInvoiceAmount = 0
  562.                         End If
  563.                         rst.Close
  564.  
  565.                         strUpdate = ""
  566.                         strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  567.                         strUpdate = strUpdate & " ( "
  568.                         strUpdate = strUpdate & " CustomerName " & " "
  569.                         strUpdate = strUpdate & " , ContractNo " & " "
  570.                         strUpdate = strUpdate & " , AccountManager " & " "
  571.                         strUpdate = strUpdate & " , InvoiceID " & " "
  572.                         strUpdate = strUpdate & " , InvoiceNo " & " "
  573.                         strUpdate = strUpdate & " , InvoiceDate " & " "
  574.                         If Trim(strInvGeneratedDate) <> "" Then
  575.                             strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  576.                         End If
  577.                         strUpdate = strUpdate & " , InvocieAmount " & " "
  578.                         strUpdate = strUpdate & " , NominalRV " & " "
  579.                         strUpdate = strUpdate & " , PVOfRV " & " "
  580.                         strUpdate = strUpdate & " , GRV " & " "
  581.                         strUpdate = strUpdate & " , InvoiceRecovery " & " "
  582.                         strUpdate = strUpdate & " , InvoiceType " & " "
  583.                         strUpdate = strUpdate & " , ADCustomerName " & " "
  584.                         strUpdate = strUpdate & " ) VALUES ( "
  585.  
  586.                         strUpdate = strUpdate & " '" & strCustomerName & "' "
  587.                         strUpdate = strUpdate & " , '" & strcontractno & "' "
  588.                         strUpdate = strUpdate & " , '" & strAccountManager & "' "
  589.                         strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
  590.                         strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  591.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  592.                         If Trim(strInvGeneratedDate) <> "" Then
  593.                             strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  594.                         End If
  595.                         strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  596.                         If blnGroup1 = False Then
  597.                             strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  598.                             strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  599.                             strUpdate = strUpdate & " , " & val(strGRV) & " "
  600.                             blnGroup1 = True
  601.                         Else
  602.                             strUpdate = strUpdate & " , 0 "
  603.                             strUpdate = strUpdate & " , 0 "
  604.                             strUpdate = strUpdate & " , 0 "
  605.                         End If
  606.                         strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
  607.                         strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  608.                         strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  609.  
  610.                         strUpdate = strUpdate & " ) "
  611.  
  612.                         If Trim(strUpdate) <> "" Then
  613.                             db.Execute strUpdate, dbSeeChanges
  614.                         End If
  615.                         rstInvoice.MoveNext
  616.                     Wend
  617.                 End If
  618.             End If
  619.  
  620.             '''''       Repair Invoice
  621.             If chkRepair Then
  622.                 strSQLConditon = ""
  623.                 If Trim(strInvDueFromDate) <> "" Then
  624.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  625.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  626.                 End If
  627.                 If Trim(strInvDueToDate) <> "" Then
  628.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  629.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceDate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  630.                 End If
  631.                 If Trim(strGeneratedFromDate) <> "" Then
  632.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  633.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  634.                 End If
  635.                 If Trim(strGeneratedToDate) <> "" Then
  636.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  637.                     strSQLConditon = strSQLConditon & " ADInvoice.InvoiceGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  638.                 End If
  639.  
  640.                 strSQl = ""
  641.                 If Trim(strSQLConditon) <> "" Then
  642.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  643.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  644.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  645.                     strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
  646.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  647.                     strSQl = strSQl & " Union "
  648.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  649.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  650.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  651.                     strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
  652.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  653.                 Else
  654.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetPrice) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  655.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  656.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADInvoice.InvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  657.                     strSQl = strSQl & " HAVING (((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
  658.                     strSQl = strSQl & " Union "
  659.                     strSQl = strSQl & " SELECT DISTINCT ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID AS InvoiceID, ADRVAllocation.ContractNo AS ContractNo, Sum(ADAsset.AssetInvoiceCost) AS SumOfAssetPrice, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  660.                     strSQl = strSQl & " FROM (ADInvoice LEFT JOIN ADRVAllocation ON ADInvoice.InvoiceID = ADRVAllocation.InvoiceID) LEFT JOIN ADAsset ON (ADRVAllocation.InvoiceID = ADAsset.AssetInvoiceID) AND (ADRVAllocation.ContractNo = ADAsset.AssetContractNo) "
  661.                     strSQl = strSQl & " GROUP BY ADInvoice.InvoiceNo, ADInvoice.InvoiceDate, ADAsset.AssetDamagesInvoiceID, ADRVAllocation.ContractNo, ADRVAllocation.ADRVAllocation, ADInvoice.InvoiceTotal, ADInvoice.InvoiceTypeID, ADInvoice.InvoiceGeneratedDate "
  662.                     strSQl = strSQl & " HAVING (((ADAsset.AssetDamagesInvoiceID) Is Not Null) AND ((ADRVAllocation.ContractNo)='" & Trim(Nz(strcontractno, "")) & "') AND ((ADInvoice.InvoiceTypeID)=2)) "
  663.                 End If
  664.                 Set rstInvoice = Nothing
  665.                 Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  666.                 If Not rstInvoice.EOF Then
  667.                     rstInvoice.MoveFirst
  668.                     While Not rstInvoice.EOF
  669.                         strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
  670.                         strBillingPeriod = ""
  671.                         If Not IsNull(rstInvoice!InvoiceID) Then
  672.                             If Trim(rstInvoice!InvoiceID) <> "" Then
  673.                                 strInvoiceID = CStr(Trim(rstInvoice!InvoiceID))
  674.                             End If
  675.                         End If
  676.                         If Not IsNull(rstInvoice!InvoiceNo) Then
  677.                             If Trim(rstInvoice!InvoiceNo) <> "" Then
  678.                                 strInvoiceNo = CStr(Trim(rstInvoice!InvoiceNo))
  679.                             End If
  680.                         End If
  681.                         If Not IsNull(rstInvoice!InvoiceDate) Then
  682.                             If Trim(rstInvoice!InvoiceDate) <> "" Then
  683.                                 strInvoiceDate = CStr(Trim(rstInvoice!InvoiceDate))
  684.                             End If
  685.                         End If
  686.                         If Not IsNull(rstInvoice!InvoiceGeneratedDate) Then
  687.                             If Trim(rstInvoice!InvoiceGeneratedDate) <> "" Then
  688.                                 strInvGeneratedDate = CStr(Trim(rstInvoice!InvoiceGeneratedDate))
  689.                             End If
  690.                         End If
  691.                         If Not IsNull(rstInvoice!InvoiceTotal) Then
  692.                             If Trim(rstInvoice!InvoiceTotal) <> "" Then
  693.                                 strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceTotal))
  694.                             End If
  695.                         End If
  696.                         If Not IsNull(rstInvoice!ADRVAllocation) Then
  697.                             If Trim(rstInvoice!ADRVAllocation) <> "" Then
  698.                                 strAllocationAmount = CStr(Trim(rstInvoice!ADRVAllocation))
  699.                             End If
  700.                         End If
  701.                         If Not IsNull(rstInvoice!InvoiceTypeID) Then
  702.                             If val(Nz(rstInvoice!InvoiceTypeID, 0)) = 1 Then
  703.                                 strInvoiceType = CStr(Trim("Sales Invoice"))
  704.                             ElseIf val(Nz(rstInvoice!InvoiceTypeID, 0)) = 2 Then
  705.                                 strInvoiceType = CStr(Trim("Repair Invoice"))
  706.                             End If
  707.                         Else
  708.                             strInvoiceType = CStr(Trim("Repair Invoice"))
  709.                         End If
  710.  
  711.                         Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
  712.                         If Not rst.EOF Then
  713.                             strInvoiceAmount = 0
  714.                         End If
  715.                         rst.Close
  716.  
  717.                         strUpdate = ""
  718.                         strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  719.                         strUpdate = strUpdate & " ( "
  720.                         strUpdate = strUpdate & " CustomerName " & " "
  721.                         strUpdate = strUpdate & " , ContractNo " & " "
  722.                         strUpdate = strUpdate & " , AccountManager " & " "
  723.                         strUpdate = strUpdate & " , InvoiceID " & " "
  724.                         strUpdate = strUpdate & " , InvoiceNo " & " "
  725.                         strUpdate = strUpdate & " , InvoiceDate " & " "
  726.                         If Trim(strInvGeneratedDate) <> "" Then
  727.                             strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  728.                         End If
  729.                         strUpdate = strUpdate & " , InvocieAmount " & " "
  730.                         strUpdate = strUpdate & " , NominalRV " & " "
  731.                         strUpdate = strUpdate & " , PVOfRV " & " "
  732.                         strUpdate = strUpdate & " , GRV " & " "
  733.                         strUpdate = strUpdate & " , InvoiceRecovery " & " "
  734.                         strUpdate = strUpdate & " , InvoiceType " & " "
  735.                         strUpdate = strUpdate & " , ADCustomerName " & " "
  736.                         strUpdate = strUpdate & " ) VALUES ( "
  737.  
  738.                         strUpdate = strUpdate & " '" & strCustomerName & "' "
  739.                         strUpdate = strUpdate & " , '" & strcontractno & "' "
  740.                         strUpdate = strUpdate & " , '" & strAccountManager & "' "
  741.                         strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
  742.                         strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  743.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  744.                         If Trim(strInvGeneratedDate) <> "" Then
  745.                             strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  746.                         End If
  747.                         strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  748.                         If blnGroup1 = False Then
  749.                             strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  750.                             strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  751.                             strUpdate = strUpdate & " , " & val(strGRV) & " "
  752.                             blnGroup1 = True
  753.                         Else
  754.                             strUpdate = strUpdate & " , 0 "
  755.                             strUpdate = strUpdate & " , 0 "
  756.                             strUpdate = strUpdate & " , 0 "
  757.                         End If
  758.                         strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
  759.                         strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  760.                         strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  761.  
  762.                         strUpdate = strUpdate & " ) "
  763.  
  764.                         If Trim(strUpdate) <> "" Then
  765.                             db.Execute strUpdate, dbSeeChanges
  766.                         End If
  767.                         rstInvoice.MoveNext
  768.                     Wend
  769.                 End If
  770.             End If
  771.  
  772.             Dim strCreditNoteAdjustedInvoice As String
  773.             strCreditNoteAdjustedInvoice = ""
  774.  
  775.             ''''''      Credit Note
  776.             If chkCreditNote Then
  777.                 Set rstInvoice = Nothing
  778.                 strSQLConditon = ""
  779.                 If Trim(strInvDueFromDate) <> "" Then
  780.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  781.                     strSQLConditon = strSQLConditon & " CreditNote.creditdate >= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  782.                 End If
  783.                 If Trim(strInvDueToDate) <> "" Then
  784.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  785.                     strSQLConditon = strSQLConditon & " CreditNote.creditdate <= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  786.                 End If
  787.                 If Trim(strGeneratedFromDate) <> "" Then
  788.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  789.                     strSQLConditon = strSQLConditon & " CreditNote.CreditNoteGeneratedDate >= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  790.                 End If
  791.                 If Trim(strGeneratedToDate) <> "" Then
  792.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  793.                     strSQLConditon = strSQLConditon & " CreditNote.CreditNoteGeneratedDate <= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  794.                 End If
  795.  
  796.                 If Trim(strSQLConditon) <> "" Then
  797.                     'strSQL = "SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNote.Contract_No, CreditNote.TotalAmount, CreditNote.AdjustedPVofRV, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount"
  798.                     'strSQL = strSQL & " FROM CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID "
  799.                     'strSQL = strSQL & " WHERE (((CreditNote.Contract_No)='" & Nz(strContractNo, "") & "')) "
  800.                     'strSQL = strSQL & " AND " & strSQLConditon & " "
  801.  
  802. '                    strSQL = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount "
  803. '                    strSQL = strSQL & " FROM (CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID) LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
  804. '                    strSQL = strSQL & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strContractNo, "") & "')) "
  805. '                    strSQL = strSQL & " AND " & strSQLConditon & " "
  806.  
  807.                     strSQl = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate "
  808.                     strSQl = strSQl & " FROM CreditNote LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
  809.                     strSQl = strSQl & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strcontractno, "") & "')) "
  810.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  811.  
  812.                 Else
  813.                     'strSQL = "SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNote.Contract_No, CreditNote.TotalAmount, CreditNote.AdjustedPVofRV, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount"
  814.                     'strSQL = strSQL & " FROM CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID "
  815.                     'strSQL = strSQL & " WHERE (((CreditNote.Contract_No)='" & Nz(strContractNo, "") & "')) "
  816.  
  817. '                    strSQL = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate, CreditNoteDetail.InvoiceID, CreditNoteDetail.InvoiceNo, CreditNoteDetail.InvoiceDate, CreditNoteDetail.RentalAmount, CreditNoteDetail.LeaseTaxAmount "
  818. '                    strSQL = strSQL & " FROM (CreditNote INNER JOIN CreditNoteDetail ON CreditNote.CreditID = CreditNoteDetail.CreditID) LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
  819. '                    strSQL = strSQL & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strContractNo, "") & "')) "
  820.  
  821.                     strSQl = " SELECT CreditNote.CreditID, CreditNote.CreditNoteNo, CreditNote.CreditDate, CreditNote.Customer_ID, CreditNoteRVAllocation.Contract_No, CreditNote.TotalAmount, CreditNoteRVAllocation.AdjustedAmount, CreditNote.CreditNoteGeneratedDate "
  822.                     strSQl = strSQl & " FROM CreditNote LEFT JOIN CreditNoteRVAllocation ON CreditNote.CreditID = CreditNoteRVAllocation.CreditID "
  823.                     strSQl = strSQl & " WHERE (((CreditNoteRVAllocation.Contract_No)='" & Nz(strcontractno, "") & "')) "
  824.  
  825.                 End If
  826.                 Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  827.                 If Not rstInvoice.EOF Then
  828.                     rstInvoice.MoveFirst
  829.                     While Not rstInvoice.EOF
  830.                         strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
  831.                         strBillingPeriod = ""
  832.                         If Not IsNull(rstInvoice!creditid) Then
  833.                             If Trim(rstInvoice!creditid) <> "" Then
  834.                                 strInvoiceID = CStr(Trim(rstInvoice!creditid))
  835.                             End If
  836.                         End If
  837.                         If Not IsNull(rstInvoice!creditnoteno) Then
  838.                             If Trim(rstInvoice!creditnoteno) <> "" Then
  839.                                 strInvoiceNo = CStr(Trim(rstInvoice!creditnoteno))
  840.                             End If
  841.                         End If
  842.                         If Not IsNull(rstInvoice!creditdate) Then
  843.                             If Trim(rstInvoice!creditdate) <> "" Then
  844.                                 strInvoiceDate = CStr(Trim(rstInvoice!creditdate))
  845.                             End If
  846.                         End If
  847.                         If Not IsNull(rstInvoice!CreditNoteGeneratedDate) Then
  848.                             If Trim(rstInvoice!CreditNoteGeneratedDate) <> "" Then
  849.                                 strInvGeneratedDate = CStr(Trim(rstInvoice!CreditNoteGeneratedDate))
  850.                             End If
  851.                         End If
  852.  
  853.                         Set rst = db.OpenRecordset("SELECT (SUM(CreditNoteDetail.RentalAmount)+SUM(CreditNoteDetail.ServiceRental)) AS TotalAmount FROM CreditNoteDetail WHERE CreditNoteDetail.CreditID = " & val(Nz(rstInvoice!creditid, 0)) & " ", dbOpenDynaset, dbSeeChanges)
  854.                         If Not rst.EOF Then
  855.                             If Not IsNull(rst!TotalAmount) Then
  856.                                 If Trim(rst!TotalAmount) <> "" Then
  857.                                     strInvoiceAmount = CStr(Trim(rst!TotalAmount))
  858.                                 End If
  859.                             End If
  860.                         End If
  861.                         rst.Close
  862.  
  863. '                        If Not IsNull(rstInvoice!AdjustedPVofRV) Then
  864. '                            If Trim(rstInvoice!AdjustedPVofRV) <> "" Then
  865. '                                strInvoiceAmount = CStr(Trim(rstInvoice!AdjustedPVofRV))
  866. '                            End If
  867. '                        End If
  868.  
  869.                         If Not IsNull(rstInvoice!AdjustedAmount) Then
  870.                             If Trim(rstInvoice!AdjustedAmount) <> "" Then
  871.                                 strAllocationAmount = CStr(Trim(rstInvoice!AdjustedAmount))
  872.                             End If
  873.                         End If
  874.  
  875.                         strCreditNoteAdjustedInvoice = ""
  876.                         Set rst = db.OpenRecordset("SELECT InvoiceNo FROM CreditNoteDetail WHERE CreditNoteDetail.CreditID = " & val(Nz(rstInvoice!creditid, 0)) & " ", dbOpenDynaset, dbSeeChanges)
  877.                         If Not rst.EOF Then
  878.                             rst.MoveFirst
  879.                             While Not rst.EOF
  880.                                 If Trim(strCreditNoteAdjustedInvoice) <> "" Then strCreditNoteAdjustedInvoice = strCreditNoteAdjustedInvoice & "; "
  881.                                 strCreditNoteAdjustedInvoice = strCreditNoteAdjustedInvoice & NZZ(rst!InvoiceNo)
  882.                                 rst.MoveNext
  883.                             Wend
  884.                         End If
  885.                         rst.Close
  886.  
  887. '                        If Not IsNull(rstInvoice!InvoiceDate) Then
  888. '                            If Trim(rstInvoice!InvoiceDate) <> "" Then
  889. '                                'strCreditNoteAdjustedInvoice = Right(Year(Trim(rstInvoice!InvoiceDate)), 2) & String(2 - Len(Month(Trim(rstInvoice!InvoiceDate))), "0") & Month(Trim(rstInvoice!InvoiceDate)) & "-" & Trim(strContractNo)
  890. '                                strCreditNoteAdjustedInvoice = CStr(Trim(rstInvoice!InvoiceNo))
  891. '                            End If
  892. '                        End If
  893.  
  894.                         strInvoiceType = CStr(Trim("Credit Note"))
  895.  
  896.                         Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
  897.                         If Not rst.EOF Then
  898.                             strInvoiceAmount = 0
  899.                         End If
  900.                         rst.Close
  901.  
  902.                         strUpdate = ""
  903.                         strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  904.                         strUpdate = strUpdate & " ( "
  905.                         strUpdate = strUpdate & " CustomerName " & " "
  906.                         strUpdate = strUpdate & " , ContractNo " & " "
  907.                         strUpdate = strUpdate & " , AccountManager " & " "
  908.                         strUpdate = strUpdate & " , InvoiceID " & " "
  909.                         strUpdate = strUpdate & " , InvoiceNo " & " "
  910.                         strUpdate = strUpdate & " , InvoiceDate " & " "
  911.                         If Trim(strInvGeneratedDate) <> "" Then
  912.                             strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  913.                         End If
  914.                         strUpdate = strUpdate & " , InvocieAmount " & " "
  915.                         strUpdate = strUpdate & " , NominalRV " & " "
  916.                         strUpdate = strUpdate & " , PVOfRV " & " "
  917.                         strUpdate = strUpdate & " , GRV " & " "
  918.                         strUpdate = strUpdate & " , InvoiceRecovery " & " "
  919.                         strUpdate = strUpdate & " , InvoiceType " & " "
  920.                         strUpdate = strUpdate & " , ADCustomerName " & " "
  921.                         strUpdate = strUpdate & " ) VALUES ( "
  922.  
  923.                         strUpdate = strUpdate & " '" & strCustomerName & "' "
  924.                         strUpdate = strUpdate & " , '" & strcontractno & "' "
  925.                         strUpdate = strUpdate & " , '" & strAccountManager & "' "
  926.                         strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
  927.                         strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  928.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  929.                         If Trim(strInvGeneratedDate) <> "" Then
  930.                             strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  931.                         End If
  932.                         strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  933.                         If blnGroup1 = False Then
  934.                             strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  935.                             strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  936.                             strUpdate = strUpdate & " , " & val(strGRV) & " "
  937.                             blnGroup1 = True
  938.                         Else
  939.                             strUpdate = strUpdate & " , 0 "
  940.                             strUpdate = strUpdate & " , 0 "
  941.                             strUpdate = strUpdate & " , 0 "
  942.                         End If
  943.                         strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
  944.                         strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  945.                         strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  946.  
  947.                         strUpdate = strUpdate & " ) "
  948.  
  949.                         If Trim(strUpdate) <> "" Then
  950.                             db.Execute strUpdate, dbSeeChanges
  951.                         End If
  952.                         rstInvoice.MoveNext
  953.                     Wend
  954.                 End If
  955.             End If
  956.  
  957.             ''''''      Short Term
  958.             If chkShortTerm Then
  959.                 Set rstInvoice = Nothing
  960.                 strSQLConditon = ""
  961.                 If Trim(strInvDueFromDate) <> "" Then
  962.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  963.                     strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STCashFlowDate>= Format(" & g_sDBDateChr & Trim(strInvDueFromDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  964.                 End If
  965.                 If Trim(strInvDueToDate) <> "" Then
  966.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  967.                     strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STCashFlowDate<= Format(" & g_sDBDateChr & Trim(strInvDueToDate) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  968.                 End If
  969.                 If Trim(strGeneratedFromDate) <> "" Then
  970.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  971.                     strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STRVAllocatedDate>= Format(" & g_sDBDateChr & Left(strGeneratedFromDate, 2) + "-" + Mid(strGeneratedFromDate, 4, 3) + "-" + Right(strGeneratedFromDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  972.                 End If
  973.                 If Trim(strGeneratedToDate) <> "" Then
  974.                     If Trim(strSQLConditon) <> "" Then strSQLConditon = strSQLConditon & " AND "
  975.                     strSQLConditon = strSQLConditon & " ShortTermRVAllocation.STRVAllocatedDate<= Format(" & g_sDBDateChr & Left(strGeneratedToDate, 2) + "-" + Mid(strGeneratedToDate, 4, 3) + "-" + Right(strGeneratedToDate, 4) & g_sDBDateChr & ",'dd-mmm-yyyy') "
  976.                 End If
  977.  
  978.                 If Trim(strSQLConditon) <> "" Then
  979.                     strSQl = " SELECT ShortTermRVAllocation.STAlloc_ID, ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowNumber, ShortTermRVAllocation.STCashFlowDate, nz([CashflowAmount],0)+nz([CashFlowServiceRental],0) AS InvoiceAmount, ShortTermRVAllocation.AllocAmount, ShortTermRVAllocation.STRVAllocatedDate "
  980.                     strSQl = strSQl & " FROM ShortTermRVAllocation INNER JOIN Contract_cashflow ON (ShortTermRVAllocation.STContract_No = Contract_cashflow.CashflowContractNo) AND (ShortTermRVAllocation.STCashFlowNumber = Contract_cashflow.CashflowNumber) "
  981.                     strSQl = strSQl & " WHERE (((ShortTermRVAllocation.AllocContract_No) = '" & Nz(strcontractno, "") & "')) "
  982.                     strSQl = strSQl & " AND " & strSQLConditon & " "
  983.                     strSQl = strSQl & " ORDER BY ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowDate "
  984.                 Else
  985.                     strSQl = " SELECT ShortTermRVAllocation.STAlloc_ID, ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowNumber, ShortTermRVAllocation.STCashFlowDate, nz([CashflowAmount],0)+nz([CashFlowServiceRental],0) AS InvoiceAmount, ShortTermRVAllocation.AllocAmount, ShortTermRVAllocation.STRVAllocatedDate "
  986.                     strSQl = strSQl & " FROM ShortTermRVAllocation INNER JOIN Contract_cashflow ON (ShortTermRVAllocation.STContract_No = Contract_cashflow.CashflowContractNo) AND (ShortTermRVAllocation.STCashFlowNumber = Contract_cashflow.CashflowNumber) "
  987.                     strSQl = strSQl & " WHERE (((ShortTermRVAllocation.AllocContract_No) = '" & Nz(strcontractno, "") & "')) "
  988.                     strSQl = strSQl & " ORDER BY ShortTermRVAllocation.AllocContract_No, ShortTermRVAllocation.STContract_No, ShortTermRVAllocation.STCashFlowDate "
  989.                 End If
  990.                 Set rstInvoice = db.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
  991.                 If Not rstInvoice.EOF Then
  992.                     rstInvoice.MoveFirst
  993.                     While Not rstInvoice.EOF
  994.                         strInvoiceNo = "": strInvoiceDate = "": strInvoiceAmount = "": strInvoiceType = "": strAllocationAmount = ""
  995.                         strRewriteContractNo = ""
  996.                         strBillingPeriod = ""
  997.                         strADCustomerName = ""
  998.                         strInvGeneratedDate = ""
  999.  
  1000.                         If Not IsNull(rstInvoice!STAlloc_ID) Then
  1001.                             If Trim(rstInvoice!STAlloc_ID) <> "" Then
  1002.                                 strInvoiceID = CStr(Trim(rstInvoice!STAlloc_ID))
  1003.                             End If
  1004.                         End If
  1005.                         If Not IsNull(rstInvoice!STContract_No) Then
  1006.                             If Trim(rstInvoice!STContract_No) <> "" Then
  1007.                                 strInvoiceNo = CStr(Trim(rstInvoice!STContract_No))
  1008.                             End If
  1009.                         End If
  1010.                         If Not IsNull(rstInvoice!STCashFlowDate) Then
  1011.                             If Trim(rstInvoice!STCashFlowDate) <> "" Then
  1012.                                 strInvoiceDate = CStr(Trim(rstInvoice!STCashFlowDate))
  1013.                             End If
  1014.                         End If
  1015.                         If Not IsNull(rstInvoice!InvoiceAmount) Then
  1016.                             If Trim(rstInvoice!InvoiceAmount) <> "" Then
  1017.                                 strInvoiceAmount = CStr(Trim(rstInvoice!InvoiceAmount))
  1018.                             End If
  1019.                         End If
  1020.                         If Not IsNull(rstInvoice!AllocAmount) Then
  1021.                             If Trim(rstInvoice!AllocAmount) <> "" Then
  1022.                                 strAllocationAmount = CStr(Trim(rstInvoice!AllocAmount))
  1023.                             End If
  1024.                         End If
  1025.                         If Not IsNull(rstInvoice!STRVAllocatedDate) Then
  1026.                             If Trim(rstInvoice!STRVAllocatedDate) <> "" Then
  1027.                                 strInvGeneratedDate = CStr(Trim(rstInvoice!STRVAllocatedDate))
  1028.                             End If
  1029.                         End If
  1030.  
  1031.                         strInvoiceType = CStr(Trim("Short Term"))
  1032.  
  1033.                         Set rst = db.OpenRecordset("SELECT * FROM ztSecondaryBilling WHERE InvoiceNo = '" & strInvoiceNo & "' AND InvoiceDate=" & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " ", dbOpenDynaset, dbSeeChanges)
  1034.                         If Not rst.EOF Then
  1035.                             strInvoiceAmount = 0
  1036.                         End If
  1037.                         rst.Close
  1038.  
  1039.                         strUpdate = ""
  1040.                         strUpdate = strUpdate & " INSERT INTO ztSecondaryBilling "
  1041.                         strUpdate = strUpdate & " ( "
  1042.                         strUpdate = strUpdate & " CustomerName " & " "
  1043.                         strUpdate = strUpdate & " , ContractNo " & " "
  1044.                         strUpdate = strUpdate & " , AccountManager " & " "
  1045.                         strUpdate = strUpdate & " , InvoiceID " & " "
  1046.                         strUpdate = strUpdate & " , InvoiceNo " & " "
  1047.                         strUpdate = strUpdate & " , InvoiceDate " & " "
  1048.                         If Trim(strInvGeneratedDate) <> "" Then
  1049.                             strUpdate = strUpdate & " , InvoiceGeneratedDate " & " "
  1050.                         End If
  1051.                         strUpdate = strUpdate & " , InvocieAmount " & " "
  1052.                         strUpdate = strUpdate & " , NominalRV " & " "
  1053.                         strUpdate = strUpdate & " , PVOfRV " & " "
  1054.                         strUpdate = strUpdate & " , GRV " & " "
  1055.                         strUpdate = strUpdate & " , InvoiceRecovery " & " "
  1056.                         strUpdate = strUpdate & " , InvoiceType " & " "
  1057.                         strUpdate = strUpdate & " , ADCustomerName " & " "
  1058.                         strUpdate = strUpdate & " ) VALUES ( "
  1059.  
  1060.                         strUpdate = strUpdate & " '" & strCustomerName & "' "
  1061.                         strUpdate = strUpdate & " , '" & strcontractno & "' "
  1062.                         strUpdate = strUpdate & " , '" & strAccountManager & "' "
  1063.                         strUpdate = strUpdate & " , " & val(strInvoiceID) & " "
  1064.                         strUpdate = strUpdate & " , '" & strInvoiceNo & "' "
  1065.                         strUpdate = strUpdate & " , " & g_sDBDateChr & strInvoiceDate & g_sDBDateChr & " "
  1066.                         If Trim(strInvGeneratedDate) <> "" Then
  1067.                             strUpdate = strUpdate & " , " & g_sDBDateChr & strInvGeneratedDate & g_sDBDateChr & " "
  1068.                         End If
  1069.                         strUpdate = strUpdate & " , " & val(strInvoiceAmount) & " "
  1070.                         If blnGroup1 = False Then
  1071.                             strUpdate = strUpdate & " , " & val(strNominalRV) & " "
  1072.                             strUpdate = strUpdate & " , " & val(strPVOfRV) & " "
  1073.                             strUpdate = strUpdate & " , " & val(strGRV) & " "
  1074.                             blnGroup1 = True
  1075.                         Else
  1076.                             strUpdate = strUpdate & " , 0 "
  1077.                             strUpdate = strUpdate & " , 0 "
  1078.                             strUpdate = strUpdate & " , 0 "
  1079.                         End If
  1080.                         strUpdate = strUpdate & " , " & val(strAllocationAmount) & " "
  1081.                         strUpdate = strUpdate & " , '" & CStr(strInvoiceType) & "' "
  1082.                         strUpdate = strUpdate & " , '" & CStr(strADCustomerName) & "' "
  1083.  
  1084.                         strUpdate = strUpdate & " ) "
  1085.  
  1086.                         If Trim(strUpdate) <> "" Then
  1087.                             db.Execute strUpdate, dbSeeChanges
  1088.                         End If
  1089.  
  1090.                         rstInvoice.MoveNext
  1091.                     Wend
  1092.                 End If
  1093.             End If
  1094.  
  1095.             Me.Repaint
  1096.             DoEvents
  1097.             If val(intCnt) <= val(Nz(ProgressBar.MAX, 0)) Then
  1098.                 ProgressBar.Value = val(intCnt)
  1099.                 txtProgerssBar = CStr(Round((val(intCnt) / val(Nz(ProgressBar.MAX, 0))) * 100)) & "%"
  1100.                 If val(intCnt / 2) = CInt(intCnt / 2) Then
  1101.                     lblImportCaption.Caption = "Collecting Data ..."
  1102.                 Else
  1103.                     lblImportCaption.Caption = "Collecting Data ...."
  1104.                 End If
  1105.             End If
  1106.             Me.Repaint
  1107.             DoEvents
  1108.  
  1109.             rstcontract.MoveNext
  1110.         Wend
  1111.     End If
  1112.     FillTempTable = True
  1113. Exit_FillTempTable:
  1114.     On Error Resume Next
  1115.  
  1116.     DoCmd.Hourglass False
  1117.     ProgressBar.MAX = 1
  1118.     ProgressBar.Value = 0
  1119.     txtProgerssBar = ""
  1120.     lblImportCaption.Caption = ""
  1121.  
  1122.     ProgressBar.Visible = False
  1123.     txtProgerssBar.Visible = False
  1124.     lblImportCaption.Visible = False
  1125.     cmdOK.Enabled = True
  1126.  
  1127.     DoEvents
  1128.     Exit Function
  1129. Err_FillTempTable:
  1130.     If Err.Number <> 0 Then
  1131.         Select Case Err
  1132.         Case 2467
  1133.         Case Else
  1134.             MsgBox "Error in FillTempTable():" & vbCrLf & Err.Description, vbInformation, "RIMS"
  1135.         End Select
  1136.         'Resume
  1137.         FillTempTable = False
  1138.         Resume Exit_FillTempTable
  1139.     End If
  1140. End Function
  1141.  
i known its quite large so please be patient and please suggest me some thing
Feb 6 '12 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
It would help to know what line the error is on.
Feb 6 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
Please read the instruction at Before Posting (VBA or SQL) Code. Also, please refrain from posting the same question in more than one place. This is even less helpful than posting over a thousand lines of code with no indication of where in the code the problem occurs.

PS. If you have a procedure that contains over a thousand lines of code then this is a big hint that you're doing something very wrong indeed. That's a totally inappropriate size for a single procedure.
Feb 6 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.