I am trying to use Dsum to get a running total of a simple credit and debits table, grouped by customer code (which is a short text field). For this I need to incorporate my credit (Received) and debit fields (OutOthers) (OutWork) and (OutBookings) from the table called tblTestAccount.
I have got it to work until I try to limit the records to those previous to the current month. The reason is to provide a running total, ordered by EntryDate so that the final line total for each customer can be used as the opening balance for the following month's account (this current month's).
This works;
CBal: DSum("[Received]-[OutOthers]-[OutBookings]-[Outwork]","tblTestAccount","[CusCode]='" & [CusCode] & "'").
When I add;
And "[EntryDate] <=#" & DateSerial(Year(Date()),Month(Date())-1,1) & "#"
It fails.
In other words, the query returns all the lines for each customer with the correct 'final' line balance which will be my next opening balance in the report, but when I try to limit the results to the previous month, the rsults for all of the lines is the same, total.
Thanks for helping.