Balance forward generation on Petty Cash Report

[Jmeyering]

I have a pretty simple database that I'm using to record petty cash transactions. I have a form filtering the report based on whatever date range is selected, but what I need is for my report to display a balance forward amount and then perform the simple calculations off of that value.

My db is set up as



TblClients:
ClientID
FirstName
LastNAme

TblPettyCashTransactions:
ClientId
DepositAmount
WithdrawlAmount
TransactionDescription

I have my report filtered by date values, what i need is somehow look up and Sum([DepositAmount])+Sum([WithdrawlAmount]) from Transactions Table based on the ClientId in the group and with dates < the first record of the grouping.

If you need any more help understanding I can upload a stripped down DB for you to examine. I really appreciate your help, I'm fairly new at this and hopefully I can learn a bit more from the experts. Thanks in advance!

[pbaldy]

You can use DSum() with the appropriate criteria to get the value. Here's the syntax:

DLookup Usage Samples

[Jmeyering]

I've tried that in the past, but I don't believe it group's on the clientid, but instead does calculations on the entire database. Anyhow, here is the syntax I'm using currently and its throwing a Type error

=DSum(Sum([DepositAmount])+Sum([WithdrawlAmount]),[tblPettyCashTransactions],[tblPettyCashTransactions]![TransactionDate]<[TransactionDate])


Also getting the value is one the first problem I'm having, is there any way to also have a running balance after each transaction. IE, print a report showing balance forward, then the first transaction factors in the balance forward to get the running balance, but transaction 2 only calculates off of the running balance.

I have the running sum fine, but it always factors off of 0 when it starts.

[pbaldy]

That doesn't look like the syntax in the link. You'd have to add an additional criteria for the client ID. The running balance after any transaction is the sum of all transactions that preceded it, including it (<=). Where else would it start but 0? Perhaps you can post the db or at least a picture of what's going wrong.

[Jmeyering]

For some reason it isn't letting me upload, anyway the link to download is

http://www.sendspace.com/file/bxw6a5

If you open the frmPettyCash, you can see thats where I'm entering transactions, on the bottom you can select a clientID and starting and ending date which will display all transactions for the selected dates.

I need to display on the report the balance of all transactions for that particular ClientID prior to the first displayed transaction. Then calculate the running balance off of that balance. so after each transaction listed, we see an updated/accurate balance not based off of 0 but based off of the previous account balance.

[pbaldy]

There was some sort of problem, as when I opened the file it would only open if the VBA was deleted. Thus, I don't know what the code is/was behind the View Ledgers button. That said, this in the group header appears to get the correct balance:

=DSum("DepositAmount-WithdrawlAmount","tblPettyCashTransactions","Clien tID = " & [Clientid] & " AND TransactionDate < #" & Forms!frmPettyCash.txtStartDate & "#")

[Jmeyering]

That works fantastic! Thanks So much its very appreciated. do you have a donate link, you deserve a couple beers for the assistance!

Is it possible to have that value factor into the running sum.

ie. The first record would display.

Balance Forward + Deposits +Withdrawls = Running sum

and the rest would simply be a regular running sum?

[pbaldy]

You could simply have a variation of that formula in the detail section that used the date of the record. Or this type of thing:

=[txtBegBal]+[text16]

where txtBegBal is the beginning balance and text16 is your existing running sum.

[pbaldy]

. do you have a donate link, you deserve a couple beers for the assistance!

Oh, and while I appreciate the offer, I don't have a donate link; I do this because I enjoy helping people. However, if you find yourself in northern Nevada, a beer wouldn't be turned down!

[Jmeyering]

Any way to reference the date in the grouping? so I can display multiple months at a time, possible the date of the first transaction in the grouping? so instead of

=DSum("DepositAmount-WithdrawlAmount","tblPettyCashTransactions","Clien tID = " & [Clientid] & " AND TransactionDate < #" & Forms!frmPettyCash.txtStartDate & "#")
We have
=DSum("DepositAmount-WithdrawlAmount","tblPettyCashTransactions","Clien tID = " & [Clientid] & " AND TransactionDate < #" & AnotherReferenceHere & "#")

To clarify, I have it grouped on Month, so it pulls the correct balance down for the first grouping based on the date in the form, however when the next month groups itself, it pulls the same balance forward and not the balance from the end of the previous month.

EDIT:
Brain Fart, simple answer.

=DSum("DepositAmount-WithdrawlAmount","tblPettyCashTransactions","Clien tID = " & [Clientid] & " AND TransactionDate < #" & [TransactionDate] & "#")

[pbaldy]

Glad you got it sorted out.