Calc. field that takes the # in the row above minus the value fro.

[Guest]

I have a database that list periods of enrollment and credits taken. I want
to create a field that takes the "remaining credits" from the row above and
subtracts the "credits taken" from the row above to calculate the new
"remaining credits." The subform filters the records by student's name from
a table using Autonumber as the primary key, and then sorts them by the
beginning date of the period of enrollment. So, the credits should be
calculated at the form level. Any help would be great. Thanks in advance,
Tim

 

[John Vinson]

I have a database that list periods of enrollment and credits taken. I want
to create a field that takes the "remaining credits" from the row above and
subtracts the "credits taken" from the row above to calculate the new
"remaining credits." The subform filters the records by student's name from
a table using Autonumber as the primary key, and then sorts them by the
beginning date of the period of enrollment. So, the credits should be
calculated at the form level. Any help would be great. Thanks in advance,
Tim

You've got a serious problem here. Tables HAVE NO ORDER - there is no
such *thing* as "the row above". A Table is an unordered bucket of
data; that's like saying that each minnow in a bucket of bait should
take a bite out of the previous minnow.

Also, Autonumbers are not guaranteed to be sequential or gapless. You
can't count on using the Autonumber value of a record, minus one,
pointing to any particular record.

On a Report you can use the RunningSum property of a textbox to do
this; on a Form, you'll need to use DLookUp and DMax to identify the
value from the chronologically most recent record: e.g. use VBA code
to set the [Remaining Credits] to

DLookUp("[Remaining Credits]", "[tablename]", "[StudentID] = " &
[StudentID] & " AND [Datefield] = #" & DMax("[Datefield]",
"[tablename]", "[StudentID] = " & [StudentID] & " AND [Datefield] < #"
& [Datefield] & "#") & "#")


John W. Vinson[MVP]