Comparing fields Access 2000

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

How do I compare the value in a field in the last record
of an Access database to the value in the same field in
the previous record. I want to create a report that
indicates that the value has changed by a calculated
percentage. I hope to do this with a query rather than a
project. I would use the query to create the report.

Thanks for any help you can give me.
Ray
 
Trying to get the previous value from the row in the query would be less
efficient and less reliable than reading the value in the events of the
report:
- less efficient because Access must calculate a function or subquery on
every row, and
- less reliable because it would be wrong if the report sorts or filters the
records differently.

To use the events of the report instead:
1. In the General Declarations section of the report's module (at the top,
with the Option statements), enter:
Dim varLastValue As Variant

2. In the Print event procedure of the Detail section, save the value for
the next iteration:
varLastValue = Me![SomeControl]

3. In the Format event procedure of the Detail section, write the result of
the calculation into an unbound text box, e.g.:
Me![txtShowDifference] = Me![SomeControl] - varLastValue

If you preview the report, and then print just page 3 or later, just the
first line of the print out may be wrong (i.e. it will not correctly show
the variation form the end of page 2, since that page was not formatted and
printed).
 
Back
Top