Running Sum

[Garyj]

I have been working on this problem for a few days and I hope someone can tell me what I am doing wrong. Below is a example of the db that I have created. My problem is how do get the monthly balance for this account. My form is performing a running sum for this account. My report only give me the running sum as of the last transaction date. I would like to know what was the balance for each month. This was created in Access97.




Thank you in advance.

 

[Bryan F]

i didn't look at your database, but a simple DSum function should do the trick. I believe you can put this straight into a text box's control source.
=DSum("[fldTransAmount]", "tblTransactions", "[CustomerID] = <customerid> AND TransDate > #" & DateAdd("d", -30, Date()) & "#")

i tried to make the names somewhat self explanatory... if you have any questions or problems post back to me.

 

[Garyj]

Thanks Bryan,

I am not sure which one of my fields should replace the ones you have in brackets. Could you please look at my report and tables?

Thanks in advance.

 

[Garyj]

I replace your brackets text with the what is in my table but I am not getting any calculations. I get an error in the text box. Here is what I put in the text box in my report header. What did I do wrong?

=DSum("[DepositAmount]","24462","[TransactionID] = <transactionID> AND TranactionDate > #" & DateAdd("d",-30,Date()) & "#")

 

[Bryan F]

put a hidden textbox on your 24462 form and give it a controlsource of TransactionID. Then you line will look like this.

=DSum("[DepositAmount]","24462","[TransactionID] = " & Forms!24462!TransactionID & " AND TranactionDate > #" & DateAdd("d",-30,Date()) & "#")

 

[Garyj]

I key in my text box on the report header the following code

=DSum("[DepositAmount]","24462","[TransactionID] = " & Forms!24462!TransactionID & " AND TranactionDate > #" & DateAdd("d",-30,Date()) & "#")

I got the following message "The expression you entered has an invalid .(dot) or ! operator or invalid parentheses.

Then I changed the code to this.

=DSum("[DepositAmount]","24462","[TransactionID] = " & [Forms]![24462]![TransactionID] & " AND TransactionDate > #" & DateAdd("d",-30,Date()) & "#")

I did not get an error but in the text box where an amount should be I got this displaying in the field. #Name?

 

[Bryan F]

it sounds like you are missing your hidden textbox on the form. It needs to have TransactionID as it's controlsource and it needs to be named TransactionID. The #Name means it's not finding the name you specified.

 

[Garyj]

In my form I did create a hidden text box call TransactionID with control source pointing to TransactionID. I also have it not showing when the form displays. Does it matter that this form also is attached to a subform call 24462 subform and in that sub form one of the fields is called TransactionID.

 

[Garyj]

Can anyone help me with this. I would appreciate the help.

Thanks in advance.