A Query that make opening closing balance and also show today movement in DB and CR

[yameen.malix]

Hi friends I have issue while making a query kindly help me.
I want to perform in this query that
Opening balance + Dr - Cr = Closing balance
And the next day my opening balance will equal to the date() -1 closing balance. And when today’s movement DR and CR take changes it will also take affect on closing balance.


Here I have two questions 1^st is how can I built a qry that will make today’s opening equal to last day closing . Can I have to built two queries or make another table that store last day closing then create relationship and again built a query that perform this If this is possible please tell me the solution this problem I will be thankful in this regard and much be appreciated
I have make a very long discussion to explain my issue I hope you all easily understand that what I am saying and what I am trying to perform , screenshot is attached that explain my issue.
Thanks in advance.

[June7]

Requires subquery or DSum

Bing: Access form opening balance

https://www.accessforums.net/access/...nce-22126.html
http://allenbrowne.com/subquery-01.html#YTD

[yameen.malix]

June7 thanks for reply to supporting me , i am not understanding this to perform kindly help me with example if you don't mind.
how can i use dsum syntax while my criteria is
todays opening = closing balnce of current date - 1
so how i can design a query?
best regards.

[June7]

This is not something I've ever had to incorporate in my databases. I don't have a handy example other than references on the web which you will have to adapt. I don't know your db so can't give you specifics for adapting.

Basically need to sum all the records prior to the current record, but also need to apply filter criteria in the DSum to synchronize with the form if it is filtered. Something like:

DSum("quantity fieldname", "table or query name", "date fieldname<#" & [date field name] & "# AND CustomerID=" & [CustomerID])

What filter criteria needs to be included in the DSum depends on your query and form design.