Expression - calculating running total

[Ted Allen]

Hi Kathy,

I don't really understand the relationship between your
two tables. Is the inception table just used to specify
the start month and value? If so, you could get rid of
the reference to the table and use parameters instead.
Or, just enter the criteria directly.

As far as referencing the value of the previous record,
you can't do that. Instead, what you would normally do
is use DSum() or a subquery to calculate the sum of all
earlier TotRet fields (which you could then add to your
baseline value if you wish). But, based on your example,
this could be tricky depending on your date's field
type.

In your example you listed 9/1984 and 10/1984 as dates.
Are the entries in these fields date values formatted to
m/yyyy, or are they just text entries? If they are text
entries, you will need to parse the month and year in
your DSum() or subquery criteria in order to determine if
the record is older.

Please post back with clarification and I will try to
provide more help.

-Ted Allen

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

 

[Guest]

Ted, I really appreciate your help on this

The Inception table is used to specify the inception date and the starting point for 100 in the IDTotRet field for that date. Since this starting point amount would only be in the one record for 9/1984, I did not want to include the field in all the records (as they would just be null values in every other record). I use a query to join the two tables by the month and year fields. There are other uses for this table, but for this situation if there is a way to do this without joining the two tables, that would be great

Composite Table fields – Month (number field with 1 through 12 as options), Year (number field with four-digit years as options), TotRet (number field formatted for percent with 2 decimals) – contains other fields that are not pertinent to what I want to do her

Inception Table fields - Month (number field with 1 through 12 as options), Year (number field with four-digit years as options), TotRet (number field with standard formatting and 2 decimals) – also contains other fields that are not pertinent to what I want to do her

Based on something you said, I did some figuring and found I can get the results I want if I use a sum to date on the TotRet field rather than trying to calculate the figure based on the previous record (which obviously cannot be done)

Can I do an IIf expression that would enter 100 in the field if the month=9 and the year=1984, and then for all other months I would need to so something like 100*(1+ the sum of TotRet up to that date)

 

[Ted Allen]

Hi Kathy,

Sorry I wasn't able to respond yesterday, I was out of
the office most of the day.

It sounds like you should be able to do what you want to
do by using a query based on the Composite table without
linking the Inception table.

Using an iif statement is one way to handle the starting
point, but it seems like it would be better to just set
the query WHERE conditions and the DSum() or subquery
conditions appropriately. If I understand what you want
to do, you want to limit the query to return records on
or after 9/1984 (WHERE (Year > 1984) OR (Year = 1984 and
Month >= 9)). But for the total, you want to add up
everything after 9/1984(WHERE (Year > 1984) OR (Year =
1984 and Month > 9)), and then add 1 to the result.

If that understanding is correct, I think the following
should work for you (copy and paste into a new query in
sql view):

SELECT Composite.Year, Composite.Month, Composite.TotRet,
1+Nz((SELECT Sum(VT.TotRet)
FROM Composite AS VT
WHERE ((VT.Year>1984) OR (VT.Year=1984 AND VT.Month>9))
AND ((VT.Year<Composite.Year) OR (VT.Year=Composite.Year
AND VT.Month<=Composite.Month))),0) AS RunSum
FROM Composite
WHERE (((Composite.Year)>1984)) OR (((Composite.Year)
=1984) AND ((Composite.Month)>=9))
ORDER BY Composite.Year, Composite.Month;

Or, if you would like more flexibility for the future,
you could use parameters to prompt for the start year and
month, such as the following:

PARAMETERS [StartYear] Short, [StartMonth] Byte;
SELECT Composite.Year, Composite.Month, Composite.TotRet,
1+Nz((SELECT Sum(VT.TotRet)
FROM Composite AS VT
WHERE ((VT.Year>[StartYear]) OR (VT.Year=[StartYear] AND
VT.Month>[StartMonth])) AND ((VT.Year<Composite.Year) OR
(VT.Year=Composite.Year AND
VT.Month<=Composite.Month))),0) AS RunSum
FROM Composite
WHERE (((Composite.Year)>[StartYear])) OR
(((Composite.Year)=[StartYear]) AND ((Composite.Month)>=
([StartMonth])))
ORDER BY Composite.Year, Composite.Month;

I didn't really see a need to multiply by 100, since the
query result can also be formatted as percent, but you
can obviously multiply the result by 100 if you desire.

As you can see, each of these queries contains a subquery
in the calculation of the running sum. The subquery
simply calculates the sum of all percentages from the
month after the start month to the month of the current
record. This total is then added to 1. For the start
month, the result of the subquery is null (no records
meet the criteria), which is converted to 0 by the Nz
function. Note that in the subquery, the composite table
is aliased as VT (Virtual Table) so that the field values
can be differentiated from the field values in the main
query (ie VT.Year refers to the year field in the
subquery table, Composite.Year refers to the Year field
value in the main query table). The DSum() function
could have also been used to perform this running
calculation.

Hopefully this will help you. Post back if it doesn't
work or if you have any questions about it.

-Ted Allen

-----Original Message-----
Ted, I really appreciate your help on this.

The Inception table is used to specify the inception

date and the starting point for 100 in the IDTotRet field
for that date. Since this starting point amount would
only be in the one record for 9/1984, I did not want to
include the field in all the records (as they would just
be null values in every other record). I use a query to
join the two tables by the month and year fields. There
are other uses for this table, but for this situation if
there is a way to do this without joining the two tables,
that would be great.

Composite Table fields â?" Month (number field with 1

through 12 as options), Year (number field with four-
digit years as options), TotRet (number field formatted
for percent with 2 decimals) â?" contains other fields
that are not pertinent to what I want to do here

Inception Table fields - Month (number field with 1

through 12 as options), Year (number field with four-
digit years as options), TotRet (number field with
standard formatting and 2 decimals) â?" also contains
other fields that are not pertinent to what I want to do
here

Based on something you said, I did some figuring and

found I can get the results I want if I use a sum to date
on the TotRet field rather than trying to calculate the
figure based on the previous record (which obviously
cannot be done).

Can I do an IIf expression that would enter 100 in the

field if the month=9 and the year=1984, and then for all
other months I would need to so something like 100*(1+
the sum of TotRet up to that date).

 

[Guest]

Things are looking good so far

I do, however, really need to multiply the results by 100. The results will not be in percent format. Where in the sql statement would I add that?

 

[Guest]

Ted, I should also mention that the name of the table is actually "tbl Composite". How does this affect the sql statement? Can I just add "tbl" before each place Composite is indicated?

 

[John Spencer (MVP)]

If the name is "tbl Composite", then you will have to change "composite" to
"[tbl Composite]". Access treats a space as a delimiter and would think you had
a table named tbl and then something else with the name composite.

 

[Guest]

I thought that was the case, so thanks for the confirmation.

I also need to be able to multiply the results of the query indicated in a previous posting by Ted Allen by 100. Where would I include that in the query?

If the name is "tbl Composite", then you will have to change "composite" to
"[tbl Composite]". Access treats a space as a delimiter and would think you had
a table named tbl and then something else with the name composite.

Ted, I should also mention that the name of the table is actually "tbl Composite". How does this affect the sql statement? Can I just add "tbl" before each place Composite is indicated?

 

[Guest]

I thought that was the case, so thanks for the confirmation!

I also need to be able to multiply the results of the query (in posting above from Ted Allen) by 100. Where in the query would I include that?

If the name is "tbl Composite", then you will have to change "composite" to
"[tbl Composite]". Access treats a space as a delimiter and would think you had
a table named tbl and then something else with the name composite.

Ted, I should also mention that the name of the table is actually "tbl Composite". How does this affect the sql statement? Can I just add "tbl" before each place Composite is indicated?

 

[John Spencer (MVP)]

Without making all the changes to the table name. I ***THINK*** what you want to
do is to use something like the following.

PARAMETERS [StartYear] Short, [StartMonth] Byte;
SELECT Composite.Year, Composite.Month, Composite.TotRet,
100 * (1+Nz((SELECT Sum(VT.TotRet)
FROM Composite AS VT
WHERE ((VT.Year>[StartYear])
OR (VT.Year=[StartYear] AND
VT.Month>[StartMonth])) AND
((VT.Year<Composite.Year) OR
(VT.Year=Composite.Year AND
VT.Month<=Composite.Month))),0)) AS RunSum
FROM Composite
WHERE (((Composite.Year)>[StartYear])) OR
(((Composite.Year)=[StartYear]) AND ((Composite.Month)>=
([StartMonth])))
ORDER BY Composite.Year, Composite.Month;

I thought that was the case, so thanks for the confirmation!

I also need to be able to multiply the results of the query (in posting above from Ted Allen) by 100. Where in the query would I include that?

If the name is "tbl Composite", then you will have to change "composite" to
"[tbl Composite]". Access treats a space as a delimiter and would think you had
a table named tbl and then something else with the name composite.

Ted, I should also mention that the name of the table is actually "tbl Composite". How does this affect the sql statement? Can I just add "tbl" before each place Composite is indicated?

 

[Ted Allen]

Hi Kathy,

Sorry I lost track of this thread for a while. For some
reason the messages weren't showing up in Google for me
until today. I'm glad that John was able to respond.

As far as your last question, I can't think of a way to
do this in a query. You can certainly reference the
previous months TotRet value for use in the calculation,
but I don't know of a way to calculate the percentage
that you want to calculate because the query isn't aware
of all of the calculations for the previous record.

If there is a way, hopefully John will know of it.

If not, you could certainly do what you want to do using
VBA. I don't know if you have any coding experience, but
even if you don't I'm sure we could help you put the code
together, it would be pretty simple.

Post back if you are interested in doing this in code,
and if so what your coding experience level is (to
determine the detail of the response).

-Ted Allen

-----Original Message-----
Thanks for your help John! Here is what I have currently:

SELECT [tbl Composite].Year, [tbl Composite].Month, [tbl

Composite].TotRet, 100*(1+Nz((SELECT Sum(VT.TotRet)

FROM [tbl Composite] AS VT
WHERE ((VT.Year>1984) OR (VT.Year=1984 AND VT.Month>9))
AND ((VT.Year<[tbl Composite].Year) OR (VT.Year=[tbl Composite].Year
AND VT.Month<=[tbl Composite].Month))),0)) AS RunSum
FROM [tbl Composite]
WHERE ((([tbl Composite].Year)>1984)) OR ((([tbl

Composite].Year)=1984) AND (([tbl Composite].Month)>=9))

ORDER BY [tbl Composite].Year, [tbl Composite].Month;

However, I have now found that using the sum of the

TotRet is not generating the desired results.

What I need in the calculation is for the results field

to be picked up from the previous month and then multiply
that by (1+TotRet). Is there any way to put in a
condition that will have it compare the current recordâ?
Ts month and year and have it select the results field
from the previous monthâ?Ts record?

For example, if you have the following records:

Record 1: Month=9, Year=1984, TotRet=1.27%
Record 2: Month=10, Year=1984, TotRet=0.91%
Record 3: Month=11, Year=1984, TotRet=1.01%

According to the selection, Record 1 would have a result of 100.
Record 2 would need to select the results from Record 1

(for 9/1984) and multiply that by (1+TotRet). That would
be 100*(1+1.27%) for a result of 101.27.

Record 3 would need to select the results from Record 2

(for 10/1984) and multiply that by (1+TotRet). That
would be 101.27*(1+0.91%) for a result of 102.19.

 

[Guest]

Hi Ted! Welcome back!

I would definitely be interested in using VBA with your assistance. I have had minimal exposure to coding. If you provide something, I could probably understand it and see the flow, but I can not write any code myself.

I would appreciate any help you could give me on this.

 

[Ted Allen]

Hi Kathy,

Let's see, I'm not sure if I'll be able to get it all
down before I go today, but I'll give it a go since I'm
off tomorrow. I'll have to give the abbreviated version
though, assuming proper input, etc. Make sure you have a
reference to DAO. Also, assumes a field in your table
named RunTotRet. You can place the code in a click event
of a button on a form.

'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 curTotalRet as Currency

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 = "WHERE (Year > " & intStartYear
strSQL = ") OR (Year = " & intStartYear
strSQL = " AND Month >= " & intStartMonth & ") "
strSQL = "ORDER BY Year, Month"

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

curTotalRet = 100

With rst
.MoveFirst
Do While Not .EOF
.edit
!RunTotRet = curTotalRet
curTotalRet = curTotalRet * (1 + !TotalRet)
.update
.movenext
Loop
End With

Set rst = Nothing
Set db = Nothing

This is all air code so I'm sure that there may be typos
or something missing, but hopefully it will be enough to
get you going. Also, you would probably want to add some
verification of the user input (for example, check to see
that month is between 1 and 12, otherwise reprompt), and
you will want to add an error handler. I can help you
with all of that next Mon, but I wanted to give you
something to try in the meantime.

Hope that helps. Good Luck.

-Ted Allen

-----Original Message-----
Hi Ted! Welcome back!

I would definitely be interested in using VBA with your

assistance. I have had minimal exposure to coding. If
you provide something, I could probably understand it and
see the flow, but I can not write any code myself.

 

[Guest]

Ted, thanks for the code. I'm just now getting back to being able to work on this. As I said, my coding experience is next to nothing. I can read what your writing and understand it, but I do not understand what DAO is and how I reference it in the code.

I'm extremely grateful for the help you've given me so far and appreciate your continued help. I have picked up a beginners book on Visual Basic to help me understand what you tell me. Thanks!

 

[Ted Allen]

Hi Kathy,

I think you'll find that it doesn't really take that long
to understand the basics of coding with VBA (Visual Basic
for Applications). Keep in mind when reading your book
on VB that if it is not geared toward the VBA environment
(used by MS Office) that the discussions regarding the
programming interface will be somewhat different. But,
the syntax is essentially the same, although the book is
likely to focus on ADO rather than DAO (which is used
widely in the Access environment - explained a little
more later).

If your book is not geared toward programming in Access,
you may want to consider one that is. John Viescas has
some good reviews of Access books at his site
(http://www.viescas.com/Info/books.htm#Access). I don't
think any of the books listed on his site focus solely on
programming, but I think that some include it. You could
also post a message in the Access|Modules newsgroup to
ask about good introductory books for Access VBA.

OK, back to ADO/DAO. DAO refers to the Data Access
Object Library. VBA is an object oriented programming
language (as are many current programming languages)
which means that it interfaces with objects from various
libraries. Objects have a variety of properties and you
can work with them in many ways, as opposed to a typical
programming variable that just has a single value or an
array of values (your VB book should give a good
description of this).

When coding in VBA, the first thing that you must do is
decide which object libraries you want to work with.
Most programs that you install on your computer will
provide one or more libraries that you can interface
with. But, in order to use a library you must reference
it. To reference the library you just go to the VBA
window (you can get there by various ways, one way is to
just press Ctrl+G from the database window, another is to
go to a command button or any other object and click in
one of the events in the properties window). Then,
choose Tools|References from the menu and you will see a
list of references. If you are using Access 97 or older,
a DAO library should be checked by default. But, in
Access 2000 or later, ActiveX Data Objects (ADO) will be
checked and DAO will not. In that case, you will need to
reference DAO by scrolling down to find Microsoft DAO 3.6
Object Library (or whatever the latest DAO library is on
your list if you don't have 3.6) and check it. You can
leave ADO checked, but I would recommend unchecking it
until you reach a point where you need to use it.

In the References Dialog Box, you can also set the order
(priority) of the references by moving them higher or
lower. This sets the order that VBA will check to see if
an object exists in a library. Some objects, such as the
recordset, exist in multiple libraries (ADO and DAO). If
you dimension a variable as an object that exists in
multiple libraries, VBA will assume that it belongs to
the library of the highest priority unless you
specifically dimension it as belonging to a specific
library (which is why I dimensioned the recordset as
DAO.Recordset in the code I posted earlier).

Most programmers explicitly dimension their object
variables this way even if the object does not belong to
another referenced library because 1) your code will not
have problems if you ever add a reference to another
library that includes the same object name, and 2) the
code runs a little faster because VBA does not have to
search through each of the libraries to find the object.

If you add the reference to DAO, and paste the code that
I posted, with the other minor changes that I mentioned,
I believe that it will work for you. Give it a try and
post back if you get an error.

If you have any questions about what certain parts of the
code are doing, post back and I will be happy to explain.

I have learned many things from this news group, so I am
happy to also help others when I can.

-Ted Allen

-----Original Message-----
Ted, thanks for the code. I'm just now getting back to

being able to work on this. As I said, my coding
experience is next to nothing. I can read what your
writing and understand it, but I do not understand what
DAO is and how I reference it in the code.

I'm extremely grateful for the help you've given me so

far and appreciate your continued help. I have picked up
a beginners book on Visual Basic to help me understand
what you tell me. Thanks!

 

[Ted Allen]

Boy, I was rushing when I typed that sql code. I should
have been concatenating each line to the variable
contents, but I left out the variable name so each line
was just replacing the previous instead of adding to it,
so the sql being passed to open the recordset was just
the ORDER BY clause. Try pasting the following in place
of the previous strSQL assignments:

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"

Following are my comments in-line regarding the code that
you posted:

With rst
-With rst means that until an end with statement is
reached any time a property or action starts with a
period (or exclamation point), the object after the with
(rst in this case) is implied before the period or
exclamation point.

.MoveFirst
-.MoveFirst means (with rst) move to the first record.
rst is a recordset, which is basically like a table in
memory. The recordset can be opened based on a Table,
Query, or in the case of your code an sql statement. If
a recordset is updateable (such as yours), updates to the
recordset will pass through to the source table the same
as if you were using an updateable query.

Do While Not .EOF
-Do the following loop until the End of Field (EOF) of
rst is reached. EOF is reached when you move to the next
record AFTER the last record.

.edit
-Enter edit mode for the current record

!RunTotRet = curTotalRet
-Assign the contents of the variable curTotalRet to the
RunTotRet field of your recordset for the current
record. Note that assigning the value to the field in
the recordset will cause the value to pass through to
that field in that record of the source table. Before
the loop, we had set curTotalRet = 1, so the first record
would be assigned one, the later records would receive
the value set in the following formula on the previous
loop.

curTotalRet = curTotalRet * (1 + !TotalRet)
-modify the value in the curTotalRet variable to be equal
to its current value times 1 plus the value in the
TotalRet field of the recordset (which is the value of
the TotalRet field for the current record in the
underlying table). Note that this will be assigned to
the following record.

.update
-Update (save) the changes to the current record

.movenext
-Move to the next record in the recordset. This is a
good time to point out that our records are ordered
ascending by year and month because of our ORDER BY
clause in the SQL. If we had not set the ORDER BY
clause, the records could be in any order and this code
would not work because it assumes that each record is in
chronological order.

Loop
-Go back to the Do While statement and check the
conditions. If condition is satisfied the loop will
continue, if not execution will resume at the next line
after this one.

End With
-End With statement signifies the end of the With rst
statement, therefore rst is no longer implied before
periods and exclamation points. All with statements must
have an end with at some point.

Note that you can copy all of these comments into your
code, just put apostrophes in front of them to tell VBA
that they are comments. Post back and let me know how it
goes.

-Ted Allen

-----Original Message-----
Ted,

Thanks so much for the info on DAO and explaining how to

reference it. Your directions were great and I had no
problem following them.

I have pasted the code that you posted and received the following error:
Runtime Error 3078: The Microsoft Jet database engine

cannot find the input table or query 'ORDER BY Year,
Month'. Make sure it exists and that its name is spelled
correctly.

I'm going to need your help on what the problem is here.

Also, could you explain what is happening with this portion of the code:

With rst
..MoveFirst
Do While Not .EOF
.edit
!RunTotRet = curTotalRet
curTotalRet = curTotalRet * (1 + !TotalRet)
.update
.movenext
Loop
End With

Set rst = Nothing
Set db = Nothing

One additional thing, the TotRet field is not a currency

field. It is an integer that is formatted for percent.

 

[Guest]

Thanks for your comments!

I replaced the strSQL assignments and now got the following error:
Runtime error 3265: Item not found in this collection.

When I debug, it goes to the following line in the code:
curTotalRet = curTotalRet * (1 + !TotalRet)

The TotRet field in my database is not a currency field. It is an integer that is formatted for percent. Could this be causing the error?

 

[Ted Allen]

Hi Kathy,

I would check the fieldname "TotalRet" that I used after
the exclamation point (usually referred to as a bang by
Access programmers). It needs to match the field name in
the source table. In looking back at your original post
it looks like it might be "TotRet" rather than "TotalRet".

There's no need to change the name of the curTotalRet
variable, just the field name after the "!".

Let me know if that takes care of it.

-Ted Allen

-----Original Message-----
Thanks for your comments!

I replaced the strSQL assignments and now got the following error:
Runtime error 3265: Item not found in this collection.

When I debug, it goes to the following line in the code:
curTotalRet = curTotalRet * (1 + !TotalRet)

The TotRet field in my database is not a currency

field. It is an integer that is formatted for percent.
Could this be causing the error?

 

[Guest]

I'm still getting an error. Here is what I currently have. Could you please read through it to see if I've accidentally changed something that I shouldn't have. The current field in the tbl Composite table is TotRet and I did add a field named RunTotRet for the results.

'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)

curTotalRet = 100

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

Set rst = Nothing
Set db = Nothing

End Sub

 

[Ted Allen]

I can see a couple of problems offhand. One is that you
changed curTotalRet to intTotalRet in all but one place,
the initial assignment of 100, so you will need to change
that or else intTotalRet will always equal 0.

One other thing, there are two periods before the
movefirst statement, I would usually only expect one.

I think this should make the code run, but your formula
may still need to be adjusted. A couple of potential
things that I see are:

1) using the integer data type for intTotalRet will limit
the value that can be stored to a little over 32,000. If
the value needs to go higher you will need to dimension
as long (which means long integer).

2) I can't remember if you said that the TotRet field in
the table is set as percent format. If so, the actual
underlying value in the field may be 100 times less than
what it is displayed as, so it is possible that the value
will need to be multiplied by 100 to get what you want.

3) using the integer data type for intTotalRet may cause
undesireable rounding errors if TotRet does not consist
of round numbers.

I would try making the first two adjustments that I
mentioned, and then see if the code runs. If it does,
you can then check the numerical results to see if they
are what you expect. If they aren't, then all we will
have to do is tinker with the formula.

If you encounter another error, post back with the error
message and the line that is highlighted in the debugger
window.

It looks like you're getting close though.

-Ted Allen

-----Original Message-----
I'm still getting an error. Here is what I currently

have. Could you please read through it to see if I've
accidentally changed something that I shouldn't have.
The current field in the tbl Composite table is TotRet
and I did add a field named RunTotRet for the results.

'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)

curTotalRet = 100

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

Set rst = Nothing
Set db = Nothing

End Sub

.

 

[Ted Allen]

Oops, just caught one more thing: you mentioned that you
added the field RunTotRet, but the field name used in the
code was RunTotalRet, change the following line:

!RunTotalRet = intTotalRet

To:

!RunTotRet = intTotalRet

-Ted Allen

-----Original Message-----
I'm still getting an error. Here is what I currently

have. Could you please read through it to see if I've
accidentally changed something that I shouldn't have.
The current field in the tbl Composite table is TotRet
and I did add a field named RunTotRet for the results.

'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)

curTotalRet = 100

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

Set rst = Nothing
Set db = Nothing

End Sub

.