How to calculate Stock on hand using Stocktake as a base quarterly

[neelsfer]

I have database with the normal stock acquire and sales fields. My issue is the stocktake table that will be used to zero all the stock on hand quantities, when it is done quarterly. These values from the stocktake table will then be used as a SOH reference from the date that the count was done, for any additions or subtractions in the inventory there after.
My million dollar question:
Any suggestions on how to do the stock on hand calculation after the last stock take, without having previous stocktake figures affecting the outcome of the results?
I imagine that the vba code should check the last date and quantity when a stocktake was done per item (if an item was out of stock on that date, then the value should be "0"), and then use this quantity as a base before any additions or subtractions has to be done.

Tables:
stocktake table = tblstocktake
some of the fields:
stocktakeid
stocktakedate
productid
clientid
Quantity

tblClient
ClientId

tblProduct
ProductId

i have an idea one should use the dLast function
but include the client, stocktakedate and product

DLast("Quantity", "tblstocktake", "stocktakeid = ???  

Any suggestions will be welcome.

[ADezii]

It would appears as though something like this is in order:

DLast("[Quantity]", "tblStockTake", "[StockTakeID] = " & 841 & _
      " AND [ClientID] = " & 999 & " AND [ProductID] = " & 41278 & _
      " AND [StockTakeDate] = #" & "7/5/2013" & "#")
 

[neelsfer]

thx adezi i will give it my best shot tomorrow and let u know.In addition to this i will have to filter all the sales and order transactions on and after the stocktake date to get to the final stock on hand total.

tblsales
salesid, salesdate,quantity

tblorder
orderid, orderdate,quantity
thx for your effort so far!