Opening balance not showing on Access Report

[rajudh]

Hello, I have Access 2013, 64 bit version.
I have made a database with 2 tables, made query for closing balance, Criteria for Date Range and Customer ID search from Form, Form with Search Button, Report that showing with criteria Customer ID and Date range.
I made a Text box in report for showing Opening balance which is date before from date field in form.
Say Report date is between 01 May 2025 to 20 May 2025, it should be show Opening balance of sum of Debit - Credit for 30 April 2025. It may be Debit or Credit balance carry forward in Text box.
I tried Code in Control Source -
=DLookUp("[Debit] - [Credit]","[Query1]","[Trans_Date]<#" & [Forms]![Frm1]![TxtFromDate] & "#")


but not working.
Please help me.New Dr & Cr - Copy.zip

[CJ_London]

‘Not working’ means nothing- you get an error? Wrong result?

best guess is either debit or credit is null or your date is not formatted in either the sql standard of yyyy-mm-dd or the us format of mm/dd/yyyy

or perhaps your criteria is wrong - it will return the first record that matches the criteria

[Micron]

In your report calculated control for "Sum Debit" try

=DSum("Debit","CustomerT","[Forms]![Frm1]![CmbCustID] AND Month(Trans_Date) = Month(Date())-1")

and do the same for Credit textbox then in opening balance textbox, subtract the two values.

This part Month(Trans_Date) = Month(Date())-1 will look at data in the prior month only. If you want to be able to do something else, you'll have to use a date value from somewhere else. In that case, you likely would not want to subtract 1 from that date.

[June7]

Your query does not calculate balance. The DLookup attempts to look at only one record. You need to aggregate records. DLookup won't do that. Use DSum. The query doesn't do any aggregating and really isn't needed. Try:

=DSum("[Debit] - [Credit]","[CustomerT]","[Trans_Date]<#" & [Forms]![Frm1]![TxtFromDate] & "# AND CustID=" & [Forms]![Frm1]![CmbCustID])

If you want to use a query and DLookup, the query must aggregate records for specified period.

I don't understand your Debit and Credit fields. Why are withdrawal types a debit and deposits a credit? How can there be withdrawals before any deposits?


I avoid dynamic parameterized queries. I prefer VBA building filter criteria and applying to form or report.


Why are Debit and Credit fields set as Long Integer? Really need to be Double or Currency probably best.

Strongly advise not to use punctuation/special characters (underscore only exception) in naming convention.

[rajudh]

Thanks CJ_London,
Not working means it was giving blank value. There is no transaction before 01/05/2025. So I changed my system date to dd-MMMM-yy, this give me date as 24-May-25 and add record on 30 Apr. 25 as Deposit in Credit column, write code suggested by "June7" as -
=DSum("[Credit]-[Debit]","[CustomerT]","[Trans_Date]<#" & [Forms]![Frm1]![TxtFromDate] & "# AND CustID=" & [Forms]![Frm1]![CmbCustID])"
It worked perfectly.

Another thing is I added a running closing balance field in details section from my "Query1' but error message is appeared as "Multy-level group by clause is not allowed in subquery".
how to solve it ?
if I simply add [Credit]+[Debit] in that field it does not take Opening balance into consideration. If I add Opening balance from Report Header it gives wrong calculation. It should give Opening balance + credit - Debit for start date and this should be carry forward as opening balance to next day add credit - debit for that day.

ex.
Op. bal. as on 30 Apr. 25 = 5,000.00

Date Trans_ID Trans. Type Debit Credit Closing Balance
02-May-25 2 Deposit 0.00 2,000.00 7,0000.00
03-May-25 3 Withdrawal 1,000.00 0.00 6,0000.00
04-May-25 4 Withdrawal 1,000.00 0.00 5,0000.00

[rajudh]

Thanks
First your code was not working. then I changed my date format to dd-MMMM-yy.
Your code worked perfectly.
thanks again.

[CJ_London]

Understand what a date field actually is - which is a decimal number. The value before the decimal point is the number of days since 31/12/1899 and the value after the number of seconds in the date divided be 86400 - the total number of seconds in 24 hours. What you see is just a format of the date.

so

?now() & " >>> " & cdbl(now())
25/05/2025 12:17:49 >>> 45802.5123726852

If you were in the US you would have seen

05/25/2025 12:17:49 >>> 45802.5123726852

so to use a formatted value (which is a string) in SQL you need two things - the # identifiers which tells SQL that the string between then is to be interpreted as a date. And the string itself should be unambiguous otherwise it will default to the US standard.

So 25/05/2025 is unambiguous because SQL knows there are not 25 months in the year so automatically swaps the two elements around.

A date like 12/05/2025 is ambiguous and will be treated as 5th December, not 12th May.

A string like 12 May 2025 is not ambiguous because the month is clearly specified.

The sql standard is yyyy-mm-dd and personally my preferred format when using sql

So I would have suggested

=DLookUp("[Debit] - [Credit]","[Query1]","[Trans_Date]<#" & format([Forms]![Frm1]![TxtFromDate],"yyyy-mm-dd") & "#")

No need then to change the format of your textbox

[rajudh]

Thank you
I did not know this.

Another thing is I added a running closing balance field in details section from my "Query1' but error message is appeared as "Multy-level group by clause is not allowed in subquery".
how to solve it ?
if I simply add [Credit]+[Debit] in that field it does not take Opening balance into consideration. If I add Opening balance from Report Header it gives wrong calculation. It should give Opening balance + credit - Debit for start date and this should be carry forward as opening balance to next day add credit - debit for that day.

ex.
Op. bal. as on 30 Apr. 25 = 5,000.00

Date Trans_ID Trans. Type Debit Credit Closing Balance
02-May-25 2 Deposit 0.00 2,000.00 7,0000.00
03-May-25 3 Withdrawal 1,000.00 0.00 6,0000.00
04-May-25 4 Withdrawal 1,000.00 0.00 5,0000.00

[June7]

Three textbox calculations:

Op. Balance
=Nz(DSum("[Debit] - [Credit]","[CustomerT]","[Trans_Date]<#" & [Forms]![Frm1]![TxtFromDate] & "# AND CustID=" & [Forms]![Frm1]![CmbCustID]),0)

Running Sum Over All or Over Group (can set this one not visible)
=[Debit]-[Credit]

Balance - sum the first two
=Text29 + Text43

[rajudh]

Thank you once again,
It worked as I want.