How do I subtract a table's next-to-last value from last one.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table keeping track of a counter which I read each week. I enter
the most recent value via a form. I want to automatically calculate the
difference between the current value and last week's value automatically
after I enter the new data. I have tried using setvalue in macros without
success. How do I designate which values for access to use?
 
Store the current value of the field when the record becomes current (or when
you enter the field), then after the field has been updated, (after update
event) calculate the difference as
DifferenceValue = me.field - StoredVale
 
I have a table keeping track of a counter which I read each week. I enter
the most recent value via a form. I want to automatically calculate the
difference between the current value and last week's value automatically
after I enter the new data. I have tried using setvalue in macros without
success. How do I designate which values for access to use?
Click to expand...

Since tables have no order, there IS no "first record" or "last
record". Access will store the records in any order it finds
convenient.

You can look up the last week's value with a Query or a DLookUp; for
example you could set the Control Source of a form textbox to

=[Counter] - DLookUp("[counter]", "[tablename]", "[DateEntered] = #" &
DMax("[DateEntered]", "[tablename]", "[DateEntered] < #" & Date() &
"#") & "#")

This rather convoluted expression finds the maximum date in the table
less than today's date, and then uses that value to look up the
counter corresponding to that date.

John W. Vinson[MVP]
 
Back
Top