Running "subtraction" on a report

  • Thread starter Thread starter Mary Lou
  • Start date Start date
M

Mary Lou

Rather than a running sum...I need to be able to SUBTRACT,
for example, the first value on a report from the second
number on that report. The easiest way to describe:

I have a box of rocks...I weigh it today, and it weighs 10
pounds.

I come back in a week, and now the box weighs 12 pounds. I
don't really care what the total weight is, I want to know
the difference between 10 and 12, which is 2. On my
report, I just want "2" for this date.

Then, in two weeks, the box weighs 15 pounds. For this
date, all I want is "3", the difference between today and
last week.

Can someone help!!
mlo
 
Hi MLO,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

You can do this with a couple of simple pieces of code in VBA. This should
get you headed in the right direction:

1- Create a Public variable in the Declaration section of a module

MyTotal as Long

This will hold the initial value of the field and then be resent for each
record to contain the last record's value.

2- Use the Reports OnActivate event to set the initial value of the
variable:

MyTotal = Me![FieldName]

3- Create a function to do the calculation and reset the value:

Function Myval()
Myval = mytotal - Me![FieldName]
MyTotal = Me![FieldName]
End Function

4- Call the function in the ControlSource of a textbox:

=Myval()



I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support
 
Dennis:

This would work fine until the report retreats. So long
as the report is simple with no grouping over entire sets
of data, it shouldn't issue a retreat, but it should be
noted that it can happen. The solution when this occurs
is much more complicated.

David
 
Back
Top