Is it possible to have a calculated control on a form that will lock the value of the calculation after a certain date?
I’m trying to use a form to automate a weekly production board. The form is based on a query and begins each week by adding the number of “OPEN” actions on a weekly production board. My control sums the number of open actions and calls it work in progress (WIP), which are actions with a start date, but no completion date. My production week begins on Sunday and ends on Saturday. Each week begins with the value of that calculation and all subsequent calculations are based on that value.
The form is set up as follows:
WIP IN OUT
SUN/MON 8 2 1
TUE 9 1 3
WED 7 0 2
THU 5 4 0
FRI/SAT 9 1 3
WIP on SUN/MON is the total count of all open actions from the previous week. On Sunday, the form sums the number of open actions as WIP. For the rest of the week, simple math adds and subtracts to keep an accurate count of WIP. It all works fine, but when I close an open action effective after the beginning of the week by updating the date completed, my WIP value is reduced by one, which throws everything off.
My query expression is: WIP: IIf([DateIn]<[StartDate] And IsNull([DateOut]),"1")
My SUN/MON calculated control is: =Sum(IIf(IsNull([WIP]),0,[WIP]))
Any ideas on how to prevent ACCESS from re-calculating WIP after the production week has begun?
I’m trying to use a form to automate a weekly production board. The form is based on a query and begins each week by adding the number of “OPEN” actions on a weekly production board. My control sums the number of open actions and calls it work in progress (WIP), which are actions with a start date, but no completion date. My production week begins on Sunday and ends on Saturday. Each week begins with the value of that calculation and all subsequent calculations are based on that value.
The form is set up as follows:
WIP IN OUT
SUN/MON 8 2 1
TUE 9 1 3
WED 7 0 2
THU 5 4 0
FRI/SAT 9 1 3
WIP on SUN/MON is the total count of all open actions from the previous week. On Sunday, the form sums the number of open actions as WIP. For the rest of the week, simple math adds and subtracts to keep an accurate count of WIP. It all works fine, but when I close an open action effective after the beginning of the week by updating the date completed, my WIP value is reduced by one, which throws everything off.
My query expression is: WIP: IIf([DateIn]<[StartDate] And IsNull([DateOut]),"1")
My SUN/MON calculated control is: =Sum(IIf(IsNull([WIP]),0,[WIP]))
Any ideas on how to prevent ACCESS from re-calculating WIP after the production week has begun?
