Thanks in advance for your help. I've been searching the forums and other helps and am still stuck as a novice.
I am writing a basic account register database and I need it to create a basic extract (account statement) that sorts all transactions first by TransactionDate then by EntryOrderID and gives a running total balance after each transaction. I have successfully used DSum to create an expression with a running total balance by EnteryOrderID but I haven't been able to correctly include the date criteria first.
Here is the expression I'm using with the EntryOrderID that gives me Total Income minus Total Expense <= current EntryOrderID:
BalanceByEntryOrderID: Format((DSum("[Income]","Register","EntryOrderID <=" & [EntryOrderID])-DSum("[Expense]","Register","EntryOrderID <=" & [EntryOrderID])),"Standard")
I tried to make the same one by date, and then I was going to try to include both criteria in the DSum, but I think my method doesn't fit in Access. If I replace EntryOrderID with TransactionDate in the expression above I get a #Error result and when I select the column in the Datasheet View of the query I get an error message: "The object doesn't contain the automation object 'Sep'" (or whatever month of the date I'm testing). I'm attaching a screenshot of this query.
The obvious problem with this example and screenshot is that when users don't enter transactions in chronological order the running balance isn't correct, since it calculates by EntryOrderID and not by Date (even though the report is sorted by date). Thus, the balance in the 3rd row should be 300, not 100.
In Excel I'd use a SumIfs formula with the 2 criteria. I've tried to use an access IF with AND but not having success so far. I've also easily created running totals each for Income and Expense in a report, but I had to use again the DSum formula I created above in an unbound box to put these two parts together into a single balance, and again it is then sorted by EntryOrderID only.
Any help is appreciated!!