how to create running balance by TransactionDate date and EntryOrderID?

[jdlantz]

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!!

[pbaldy]

Along the lines of

EntryOrderID <=" & [EntryOrderID] & " AND TransactionDate <= #" & TransactionDate & "#"

By the way, you should be able to do the subtraction within a single DSum:

DSum("Income - Expense", ...

[June7]

Post your attempted date expression. Something like:

Format((DSum("[Income]","Register","TransactionDate<=#" & [TransactionDate] & "#")-DSum("[Expense]","Register","TransactionDate<=#" & [TransactionDate] & "#")),"Standard")

Doesn't the DSum expression also need some criteria for account or customer ID?

[jdlantz]

Thanks, I'm getting closer but having trouble with the date syntax. Here is my new sample screenshot:



I still have problems when I put the two criteria together. Look at column "BalanceByID-AND-Date". Row 4 should show "200", but it shows "300" because the first criteria (ID<=) eliminates the row 3 transaction. This is the formula I have in that column:
BalanceByID-AND-Date: Format((DSum("Income-Expense","Register","EntryOrderID <=" & [EntryOrderID] & " AND TransactionDate <= #" & [TransactionDate] & "#")),"Standard")

So, I try to reverse the order of the criteria to date first, then ID as follows:
BalanceByDate-AND-ID:Format((DSum("Income-Expense","Register", TransactionDate <= #" & [TransactionDate] & "#" AND "EntryOrderID <=" & [EntryOrderID] & ")),"Standard")

But when I do that I get an error as I enter it into the design field: "The expression you entered has an invalid date value." I don't understand why I get this error since I'm using the same syntax just in a reverse order.

Any ideas on my syntax?

Or, is there some other way I should approach this in order to sort my data by date first then by ID and have a running total income - running total expense for all entries from that row and above? (I know I'm still thinking about Excel models.)

As far as other criteria for account or customer ID, I do have other fields that I will use in my transactions but this is for a very simple application and the only reporting I'm doing from it includes all entries so I'm not trying to make any criteria for certain entries, only show a running balance overall. Is there another criteria that I need to include anyway to make this work right?

Thanks again.

(BTW the last column, Expr 1, wasn't meant to be on the screenshot, sorry. It is a another criteria by date only but with two DSum expressions instead of one subtracting Expense from Income.)

[pbaldy]

You can't simply move stuff around. You have to apply the logic of concatenating values and literal text to achieve a desired result. Try

DSum("Income-Expense","Register", "TransactionDate <= #" & [TransactionDate] & "# AND EntryOrderID <=" & [EntryOrderID] )

though I'm not sure it will change anything.