Opening Bal and closing Balance

[Sam]

How to get the closing balance of a particular day as the opening balance on
the subsequent day in a stock report

Is it possible to to with queries using alias

Pls help

 

[Michel Walsh]

In a report? If the date field is called theDate, if the field qty has +
for received material, and - for sold material, then


=DSum( "qty", "tableNameHere", "theDate<" & FORMAT( theDate,
"\#mm/dd/yyyy\#")


should do. It sums what is under the field qty, for the given table, for all
records having their date less than the date for the actually printed data.



Hoping it may help,
Vanderghast, Access MVP

 

[Sam]

Thanks for the response. Initially I was having two column for Received Qty
and issued Qty. Now I have changed it to single field with + and - values the
dates are repeated twice. I am still not clear on the explanation given on
Report. Is it possible to get the closing bal and open bal as a query result
.. If not where I have to type the DSum funciton u have given in Report.

 

[Michel Walsh]

You probably can on a query, but it is much easier with a report. I assume
your report has many sections, at least, on sub-group, ' per month' . In
that *header* section of the group, place the DSum expression to get the
closing sum of whatever comes from previous months. Next, fill the *detail*
section of the report with data for the actual month.

If you have two columns, or prefer two columns, qtyReceived and qtySold, you
can use: DSUM( "qtyReceived-qtySold", "tableName", ... )



Hoping it may help,
Vanderghast, Access MVP