Running "subtraction" on a report

[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

 

[Dennis Schmidt]

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

 

[GreySky]

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