I need to use the last trsaction day's opening balance as opening stock for the next transaction day.
Have set up this bsic transaction table with the following fields:
TransID, TransDate, ProductName, StockIn, Orders
I've created a query which calculates the daily_net_stock (stockIn - Orders). The query also has calculated date fields showing transaction startdate and enddate.
I need help with an formula I tried to achieve the opening stock by writing;
OpeningBalance: IIF([enddate]=[startdate],0,(daily_net_stock.transdate()-1))
What I desire is for the formula to return 0 as opening balance for the first transaction day and to return the closing balance for the previous day in subsequent daily transactions.
Thank you.