Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
first two months closing balance and opening balance correct 3 month wrong opening balance

What I have tried:

VB
Private Sub cmdprint_Click()



If datEnv.rsrptqrytrialacbook_Grouping.State = adStateOpen Then
    
   
     datEnv.rsrptqrytrialacbook_Grouping.Close
    
End If
    
    datEnv.rptQryTrialAcBook_Grouping CDate(ctlFrom.value), CDate(ctlTo.value)
    
        datEnv.rsrptqrytrialacbook_Grouping.Filter = adFilterNone
        
        
    
        datEnv.rsrptqrytrialacbook_Grouping.Filter = "Account='" & Trim(cboAccount) & "'"
    
      
            
            Set rec = DB.Execute("Select Sum(Debit) as Debit,Sum(Credit) as Credit from QryTrialAcbook Where Account='" & cboAccount & "' and TrnDate <#" & ctlFrom & "# Group by Debit,Credit ")
            Set rec = DB.Execute("Select Sum(Debit) as Debit,Sum(Credit) as Credit from QryTrialAcbook Where Account='" & cboAccount & "' and TrnDate <#" & ctlFrom & "# Group by Debit,Credit ")
            
            
            Set rec = DB.Execute("SELECT Account, Sum(Debit) AS Debit, Sum(Credit) AS Credit From TrialAcBook Where Account = '" & cboAccount & "and TrnDate<#" & ctlFrom & "#' GROUP BY Account")
            
            Set rec = DB.Execute("SELECT Account, Sum(Debit) AS Debit, Sum(Credit) AS Credit From TrialAcBook Where TrialAcBook.TrnDate < #" & CDate(ctlFrom) & "# and Account='" & cboAccount & "' GROUP BY Account")

            
            
            If rec.RecordCount > 0 Then
          
                                If rec("Debit") < rec("Credit") Then
                                    
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDes").Caption = "Oppenning Balance"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblCredit").Caption = Format(Abs(rec("Debit") - rec("Credit")), ".00")
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDebit").Caption = ".00"
                    
                                    
                                ElseIf rec("Debit") > rec("Credit") Then
                                
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDes").Caption = "Oppenning Balance"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDebit").Caption = Format(Abs(rec("Debit") - rec("Credit")), ".00")
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblCredit").Caption = ".00"
                                    
                                Else
                                    
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDes").Caption = "Oppenning Balance"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblCredit").Caption = ".00"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDebit").Caption = ".00"
                                    
                                End If
            
            Else
            
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDes").Caption = "Oppenning Balance"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblCredit").Caption = ".00"
                                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDebit").Caption = ".00"
            End If
        '---------------------------------------------end header section

        
                Set rec = DB.Execute("Select Sum(Credit) as Cr,Sum(Debit) as Dbt from QryTrialAcBook Where Account='" & cboAccount & "' and TrnDate >= #" & Format(ctlFrom, "DD-MMM-YYYY") & "# and TrnDate <= #" & Format(ctlTo, "DD-MMM-YYYY") & "# Group by Account")
                
                
                If rec.RecordCount > 0 Then
                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblCr").Caption = Format(rec("Cr") + Val(rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblCredit").Caption), ".00")
                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblDbt").Caption = Format(rec("Dbt") + Val(rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Header").Controls("lblDebit").Caption), ".00")
                Else
                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblCr").Caption = ".00"
                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblDbt").Caption = ".00"
                End If
            
            
                    rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblBal").Caption = Format(Val(rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblDbt").Caption) - Val(rptLedgerBookDateRange.Sections("QryTrialAcBook_Grouping_Footer").Controls("lblCr").Caption), ".00")
            
        '---------------------------------------

    rptLedgerBookDateRange.Show 1


End Sub
Posted
Updated 11-Sep-21 22:43pm
v2

First a suggestion: don't double space your code: it doesn't make it look bigger or better - it just makes it harder to read as you can fit less of it on the screen. And that means that you have to scroll around a load more to see what is going on.

The problem you have noticed we can't help you with - it needs your actual data running with your whole app and we don't have access to any of that!

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
 
Share this answer
 
Looking at the code you seem to concatenate values directly to the SQL statement. For example here
Set rec = DB.Execute("Select Sum(Debit) as Debit,Sum(Credit) as Credit from QryTrialAcbook Where Account='" & cboAccount & "' and TrnDate <#" & ctlFrom & "# Group by Debit,Credit ")

If the data contains invalid characters or the date format is not correct, the query will fail. Furthermore this leaves you open to SQL injection - Wikipedia[^]

Instead of using the SQL statements directly, try using a querydef and applying parameters. Have a look at Parameters collection (DAO) | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900