How to add a balance field in a continuous form

[brunces]

Guys,

I have a table (tblMoney) with the following fields and values:

fldCode :: fldDate :: fldValue
1 :: 03/15/2011 :: 100.00
2 :: 03/17/2011 :: 40.00
3 :: 03/19/2011 :: -20.00
4 :: 03/21/2011 :: 15.00
5 :: 03/16/2011 :: -30.00
6 :: 03/18/2011 :: 200.00
7 :: 03/20/2011 :: -60.00
8 :: 03/22/2011 :: -10.00


fldCode is an AutoNumber field.
fldDate is the date when the values were paid or received.
fldValue is the amount of money (debits and credits).

As you can see, values are not necessarily input in a chronological order. They are input randomly, when it comes to dates. But the fldCode follows an ascending order, of course, because it is automatic.

I need a continuous form (frmMoney) with the following layout:

Date :: Value :: Balance
03/15/2011 :: 100.00 :: 100.00
03/16/2011 :: -30.00 :: 70.00
03/17/2011 :: 40.00 :: 110.00
03/18/2011 :: 200.00 :: 310.00
03/19/2011 :: -20.00 :: 290.00
03/20/2011 :: -60.00 :: 230.00
03/21/2011 :: 15.00 :: 245.00
03/22/2011 :: -10.00 :: 235.00

fldCode is present in the form, however it is hidden (no need to be shown).
Records must be sorted by ascending date, of course, otherwise the balance would not make any sense.
If there is more than one record with the same date, fldCode can be the second sorting option. (But that does not really matter.)

And now, my doubt is... how can I have all of this?

- The table is ready.
- There is a query (qryMoney) based on that table, just to sort the records in ascending order of date, nothing else. (Is it necessary?)
- The continuous form is based on that query and it is partially ready, I mean, I have already made the layout. That is all.

What I really do not know is how to get that balance field to work. I have no idea what to do. Please, help me.



Thank you very much for your attention.

brunces

[ajetrumpet]

if you're code field is an actual autonumber field, what you need to do here is use the technique explained here: http://support.microsoft.com/kb/290136

But what you'll have to do is use the DSUM() method by using the code field and the date field as the criteria fields. I believe the primary example in that KB article assumes autonumbers that are listed in ascending order.

You will use the date field first, and then the code field, simply because some dates are duplicated. And when the query statement runs into that situation, the differentiation will take place by also using the code field's autonumber value. The lower the value, the older the entry of the duplicated date.

And with dates that are not duplicated, they will fall into place nicely anyway.

[brunces]

ajetrumpet, thank you very much for your attention.

I've seen the website you suggested. The second method seems to be very close to what I'm looking for, but I confess I couldn't reproduce the same in my DB file successfully. Actually, I didn't understand the reason for grouping records. In my case, I don't want to group anything.

Please, could you help me with that?

I've attached my DB file for a better comprehension.

Thanks again.

brunces

[kennejd]

Because you're displaying a running total, the query needs to know what all the records prior to that moment in time equal. So it groups them. You see the detail in your query, but the background query is grouping and summing. You should be able to follow the knowledgebase article to achieve your result...might take a bit of trial-and-error...

[brunces]

Sorry, kennejd, but I really didn't understand. I've tried to follow the steps, but I could not get to a satisfatory result. I think I'm having difficulties on how to adapt it to my needs and that's why I'm here for, looking for help. But, thanks anyway. I'll keep on looking for another solution.

brunces

[ajetrumpet]

brunces,

try this:

SELECT tblMoney.fldcode, tblMoney.flddate, 

[fldvalue] + 

iif(DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#") is null, 0, 
DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#")) 

+ 

IIf((DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) Is Null, 
0, DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) AS Expr1

FROM tblMoney

ORDER BY tblMoney.fldDate;

that does exactly what I was explaining earlier.

[brunces]

ajetrumpet, thank you very much for your help, bud. That's exactly what I needed.

I didn't know I had to add those "#" to the expression, and I wasn't considering the fact that the result could be null.

You're the man! Thanks again.

brunces

[ajetrumpet]

OK. good luck from here.

[brunces]

ajetrumpet, here I am again. I'm sorry to say, but there's something wrong with that query.

I've made some changes and now the query result has gone crazy.

For example, I've repeated some dates and changed others. And now, take a look at the result.

Date :: Code :: Value :: Balance
3/05/2011 :: 8 :: -10 :: 225
3/15/2011 :: 1 :: 100 :: 90
3/17/2011 :: 2 :: 40 :: 130
3/17/2011 :: 5 :: -30 :: 100
3/18/2011 :: 6 :: 200 :: 300
3/19/2011 :: 3 :: -20 :: 280
3/19/2011 :: 4 :: 15 :: 295
3/20/2011 :: 7 :: -60 :: 235

I've attached the DB file as well.

Any thoughts?

brunces

[ajetrumpet]

I don't know what to tell you, bud.

Here's what I see in 2007 when I open it, even with your changes:



I will say that you might need to pay a little more attention to very small things that you are doing with data. I've seen posts on forums that could be resolved by simply closing a form and opening it again. Make sure you try to figure it out yourself before banging on the keyboard here.

I'm assuming this one is a simple fix...

[brunces]

ajetrumpet, I don't know what to say either. I use Access 2010 here, as you can see in the screenshot. The tmpBalance strangely begins with the value 225.

I'm from Brazil, and dates here are inverted, as you can see. But that's not the point. I'm just saying...

Well, thanks anyway for your attention.

brunces

[ajetrumpet]

well,

this is what you do when something like this happens: you refresh stuff, compact and repair the db, close it and open it again.

I don't believe that the ORDER of numbers in dates are relevant when using the date functions that I used for you. DATEPART() might pose a problem, but that is not being used here.

On one of my machines, the 64 bit machine, it did initially appear like what you posted. So I recopied my code from here back into the query, saved it, and problem was fixed immediately.

what you also need to do, if you don't realize, is to think back to what EXACTLY you did between the time you pasted my code in, tested it, saw that it was working, and then notice it not working. obviously something changed it, right??

retrace your steps if you can. that might give you a hint as to what it doesn't like!

furthermore, if you pasted my code on in and did NOT test it or look at it to assure that it was working before you changed your dates and such, then it probably never worked to begin with. That's also something that needs checked out.

[thechazm]

I read some of what you were trying to do and I just finished designing a personnel check register and budget system for myself. You are welcome to this code and I hope it helps.

 
Private Sub Transaction_Amount_AfterUpdate()
Dim TransAmount As Currency, rs As DAO.Recordset, db As Database, iCurrent As Long
Dim recBefBalance As Currency
RunCommand acCmdSaveRecord
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From tblCheckRegister")
iCurrent = Me.CurrentRecord
rs.Move iCurrent - 2
If rs.BOF = True Then
    rs.Move 1
    RunningBalance = 0
Else
    RunningBalance = rs("After Balance")
    rs.Move 1
End If
Do While rs.EOF = False
    TransAmount = rs("Transaction Amount")
    rs.Edit
    With rs
        ![After Balance] = RunningBalance + TransAmount
    End With
    rs.Update
    RunningBalance = rs("After Balance")
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Refresh
End Sub

Just remember to add a global variable called RunningBalance in a module as currency and the rest it just changing the field names and tables.

I tracks the balance and if you change an old entry it also updates all the other records to keep it exact.

l8r,

TheChazm