# Date Calc - Weekly Production Board

#### Tom Vert

##### New Member
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?

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Replies
3
Views
240
Replies
1
Views
105
Replies
3
Views
1K
Replies
1
Views
594
Replies
0
Views
592

1,196,345
Messages
6,014,730
Members
441,842
Latest member
mattdale

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back