How can I calculate the difference between the initial value of a field and the updated value on update on a form

How can I calculate the difference between the initial value of a field and the updated value on update on a form?  I have an actual hours field for a unique technical support case record.  When a user updates the value of actual hours I want to calculate the difference between the original value and write one record to a case hours history table with the after update value for actual hours and the date time of the update.  I have the update writing the record to the history table but I'm having problems using the fields' BeforeUpdate, OnDirty, Or AfterUpdateEvents to calculate the difference between the initial value and after update value and writing to the history table.    Alternatively, once many records are written to the history table for one record how can a report be written to calculate any differences in actual hour values  from week to week between the history records?
conardbAsked:
Who is Participating?
 
conardbConnect With a Mentor Author Commented:
I found a solution, thanks.  On the after update event, I calculate the change in the fields value using .value and .oldvalue, store in a variable then run an sql insert command.
0
 
als315Commented:
In you case may be better to work with copy of record and save data with some button. In this case you will have more control on changes made. Otherwise user can change one field many times without record change. How many records should be written to history?
It is not a problem to show difference in hours from week to week, but you should clarify algorithm: is it difference between first and last value or something other?
0
 
conardbAuthor Commented:
I am writing a copy of the record to a seperate table on the after update event of the hours field so when the hours are updated a record is written to history.  I'm writing the date time of the update then group reporting by week.  One record will be written for each update to the hours field.  I'm trying to calculate the difference in the hours field and write that difference to history then sum the differences in hours entered by week in a report.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
als315Commented:
Can you upload DB with main table, history table with some sample data and show expected result?
0
 
als315Connect With a Mentor Commented:
It is possible, of course, but generally you should not store calculated fields.
0
 
conardbAuthor Commented:
Point taken.
0
 
conardbAuthor Commented:
I resolved and took suggested comment from expert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.