Expression - calculating running total

[Ted Allen]

Hi Kathy,

Glad to hear that it is working, even if not quite
correct yet.

For some reason I can't see your last post in the
Microsoft Newsgroup website. Luckily I decided to check
out the MSDN site and saw your post through a search
there. Anyway, my response is below, and I have posted
your last post copied from MSDN for others that may see
this (I couldn't reply there because I couldn't remember
my passport info - it's been a long time since I used
that). I have posted this at the top of the thread in
case there is some limit as to how many levels it will
allow.

If your numbers go to 8 decimals, you should probably
change intTotalRet to be double instead (floating point
number capable of holding up to about 15 significant
digits). Try changing the lines that this variable
occurs in to the following (taken out of context to list
individual lines).

Dim dblTotalRet As Double
dblTotalRet = 100
!RunTotRet = dblTotalRet
dblTotalRet = dblTotalRet * (1 + Nz(!TotRet, 0))

This should eliminate the problem with rounding in the
loop. I believe that when you assign the value to the
RunTotRet field, it will be rounded as appropriate (if it
is an integer field it will round to an integer). So, if
your results are still being rounded too much, you can
change the number type for the RunTotRet field in your
table to single, or double, or currency.

Hopefully that will take care of your rounding problem.
Post back and let me know whether the results are correct.

-Ted Allen

---------------------------------------------------------
Listed below is your original message for reference.
---------------------------------------------------------
I did find one record that had a blank field. I fixed
that, but also changed the code just in case it's an
issue in the future as you suggested. After that, the
code ran!!!!

However, you were correct that there is a problem with
the results rounding. Even though the RunTotRet field is
formatted for two decimal points, it is rounding to a
whole number. The TotRet field does not contain whole
numbers. It is formatted for two decimal places, but
some of the actual data entered in the field can have up
to 8 decimal points.

What are your suggestions?


'Dimension Variables
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strMsg As String
Dim intStartMonth As Integer
Dim intStartYear As Integer
Dim intTotalRet As Integer

strMsg = "Please enter a number from 1 to 12 for the"
strMsg = strMsg & "month that you would like "
strMsg = strMsg & "to use as the baseline for the "
strMsg = strMsg & "calculation of cumulative returns."

intStartMonth = InputBox(strMsg, "Enter Start Date")

strMsg = "Please enter a 4-digit number for the "
strMsg = strMsg & "year that you would like "
strMsg = strMsg & "to use as the baseline for the "
strMsg = strMsg & "calculation of cumulative returns."

intStartYear = InputBox(strMsg, "Enter Start Date")

strSQL = "SELECT * FROM [tbl Composite] "
strSQL = strSQL & "WHERE (Year > " & intStartYear
strSQL = strSQL & ") OR (Year = " & intStartYear
strSQL = strSQL & " AND Month >= " & intStartMonth & ") "
strSQL = strSQL & "ORDER BY Year, Month"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

intTotalRet = 100

With rst
..MoveFirst
Do While Not .EOF
.edit
!RunTotRet = intTotalRet
intTotalRet = intTotalRet * (1 + Nz(!TotRet, 0))
.Update
.MoveNext
Loop
End With

Set rst = Nothing
Set db = Nothing

End Sub

-----Original Message-----
I have a query that joins two tables. They are joined

together by the Month and Year in order to have a 100%
starting point in a particular month (9/1984) in the
IDTotRet field. I need to calculate a figure based on
two fields so that it picks up the number from the
previous month/year record.

Composite Table: contains fields - month, year, TotRet
Inception Table: contains fields - month, year, IDTotRet

For example, if the IDTotRet for 9/1984 were 100% and

the TotRet for 10/1984 were 1.27%, what I want in my new
field would be 101.27. [IDTotRet]*(1+[TotRet]).

Now for the next month, I want the expression to pick up

the calculation in the new field for 10/1984 - the
101.27 - that was calculated for the previous month and
perform the same calculation for that month. In this
instance, the IDTotRet calculated for 10/1984 was
101.27. The TotRet for 11/1984 was 0.91%, what I want in
my new field would be 102.19.

I started with this expression ITotRet: IIf([Month]=9

And [Year]=1984,100,[IDTotRet]*(1+[TotRet]))

However, I donâ?Tt know where to include the information

to get it to pick up from the previous record.

 

[Ted Allen]

Hi Kathy,

For some reason I was thinking that the TotRet value to
calc a given month was in the prev months record. The
reason the calc is off is that it is calcing the
RunTotRet for say 10/84 using the TotRet value from 9/84
(because the calc is before the movenext statement).
This should be an easy fix by modifying your loop as
follows (replace all code from the With to the End With
statements):

With rst
.MoveFirst
.Edit
!RunTotRet = intTotalRet
.Update
.MoveNext
Do While Not .EOF
intTotalRet = intTotalRet * (1 + Nz(!TotRet, 0))
.edit
!RunTotRet = intTotalRet
.Update
.MoveNext
Loop
End With

Let me know if that works.

-Ted Allen

-----Original Message-----
Ted, changing to double took care of the rounding issue. Thanks!!

The results are so close, but not quite right just yet.

I'll try to explain. When it runs, the initial results
when you enter month 9 and year 1984 for the
intStartMonth and intStartYear should be 100. This does
occur, however, I also get 100 for the results for the
next month (for 10/1984). After that, the results seem
to be what I want, but they are a month behind.

Here is a sample of the results:

Month Year TotRet RunTotRet
9 1984 Null 100.00
10 1984 1.27% 100.00

11 1984 0.91% 101.27

12 1984 1.01% 102.19

1 1985 1.66% 103.23

 

[Ted Allen]

Alright, that's great news.

You can definitely get rid of the prompt if you always
want to start at the same month. In fact, in that case
you can get rid of the variables all together and just
put the values right into the strSQL statement, such as
the following:

strSQL = "SELECT * FROM [tbl Composite] "
strSQL = strSQL & "WHERE (Year > 1984) "
strSQL = strSQL & "OR (Year = 1984 "
strSQL = strSQL & "AND Month >= 9) "
strSQL = strSQL & "ORDER BY Year, Month"

But, if the line that you are always going to start with
is also the first line, you could get rid of the criteria
altogether and just use:

strSQL = "SELECT * FROM [tbl Composite] "
strSQL = strSQL & "ORDER BY Year, Month"

Note that you could also use the second strSQL statement
above even if you have older records, but in that case
you would check the values in your loop and just assign 0
until you find the record matching your start month and
year, and then assign 100, and then use your formula from
that point on. Whatever you think will be most flexible
for the future.

You can certainly add additional code to the same command
button event. After the code that you have, you could
reassign strSQL, open another recordset, work with it,
close it, etc. I would add all of the code to one button
if you would always want to execute it together,
otherwise I would use separate buttons for code that you
may want to run at different times.

One note about the code, I normally would have the line:

rst.close

just above the set rst = nothing statement. I think I
left it out of the original code that I posted. I don't
know that it really matters, but it is the standard
practice, and doesn't hurt.

It would also be a good idea to add an error handler to
the code once you are all done. I usually wait until I
am done so that while debugging it will take me right to
the debugger window on the offending line when errors
occur. But, once everything is running, if other users
will be using the button it is better to add an error
handler so that they just get a message box when an error
occurs rather than being taken to the code window, which
really seems to give people quite a scare if they don't
know what it is (plus they could accidentally mess up the
code).

The error handler statement is really pretty simple. It
just involves putting an On Error statement near the top
of the code, and usually also having error handling code
at the very end, such as the following:

Dim Statements

On Error Goto ErrorHandler

Your Code...

Exit Sub
'Very important because if an error is not encountered
'you want to exit the sub before getting to the
'errorhandler code

Error Handler:
strMsg = "The following error was encountered:"
strMsg = strMsg & Chr(13) & Chr(10) & Chr(10)
StrMsg = strMsg & Err.Number & " - " & Err.Description
strMsg = strMsg & Chr(13) & Chr(10) & Chr(10)
StrMsg = strMsg & "Please make a note of this error and "
StrMsg = strMsg & "notify your database administrator."
MsgBox StrMsg
Resume Next

End Sub

Note that the err object is a built in object that takes
on a variety of information related to an error when one
is encountered. The above code concatenates an error
message using the number and description of the error by
using the err object.

The last part of the error handler code is the Resume
Next statement. This is optional. In some cases (such
as the On Current event of a form) I use this to run the
remaining code lines that do not generate errors after
the message is displayed. In other cases, more like the
type of code that you are writing, I leave this out in
which case the execution then passes to the End Sub line
and execution quits. If you are working on code that
manipulates important data, and it is important that all
updates take place or none at all, you can start a
transaction at the beginning of the code, and just above
the error handler commit the transaction and exit, but in
the error handler roll back the transaction if an error
is encountered.

There are other ways of configuring an error handler such
as just adding a line as follows:

On Error Resume Next

Or,

On Error Exit Sub

Also, it is standard practice if not using resume next
with an error handler, to set your object variables =
nothing prior to exiting (since your code may not have
gotten to those statements in the main part of the
procedure)

This may have been more info than you wanted/needed at
this point, but hopefully it will help. I'll be happy to
help you continue to finalize your code, but this post is
getting kind of old and hard to find, so you can also
feel free to e-mail me at:

tallen a t san d o t lacity d o t org

which is obviously in a modified english translation of
the e-mail address, just put it together as it reads. I
learned quite a while back not to type my e-mail address
in the usual form in these newsgroups because it gets
picked up by tons of spam generators.

-Ted Allen

-----Original Message-----
Ted - we have success!!!!!!!!!!!

Changing that last bit of code worked perfectly. I just

tested the results against our original spreadsheet, and
all the results match. I have a couple of more fields
that require the same calculation, so I shouldn't have
any problem just changing the field names on the current
code you've helped me with.

Just two more questions for you.

1. If the intStartMonth for this is always 9 and the

intStartYear is always 1984, can I just change it to:

intStartMonth = 9
intStart Year = 1984

and eliminate the strMsg to request the information?

2. I have three fields that will be handled the same

way. I have this code associated with a command button
on a form. Can I include all three on one command button
so they all run at the same time or do I have to do them
separately?