Simplify conditions, if possible

[Chris1112]

I have a form to calculate cycle time based on 26 different possibilities, which are events in a sequence. I need to start with the first event that does not have a date entered and calculate estimated date of occurrence from there. The entries that do have events entered need to have a simple calculation done to them.

Instead of making a huge array of If..Then statements, I would prefer a much more simplistic code structure. I am not exactly sure where to begin. Rather than stating something like:

If IsNull(date1) then
box1 = box0 + 1
box2 = box0 + 6
box3 = box0 + 8
Else If IsNull(Date2) Then
box2 = box1 + 6
box3 = box1 + 8
box4 = box1 + 3....And so on, 26 times for 26 boxes, and with other variables that make me have to repeat this procedure eight times.

Any suggestions, examples of similarly performed items, or even naming a function or two that might point me in a direction are very helpful. Thank you in advance.

 

[RossWindows]

Hmm.
I was going to suggest nested loop statements, but without some insight to the math involved here, I'm not so sure.

Can you enlighten us; What is it that you're ultimately trying to accomplish?

Maybe if you wrote out the entire procedure the 'old-school' way then we can look at making it more efficient.

 

[Chris1112]

I am trying to write formulas to project completion dates of tasks based on a number of work days. Unfortunately, the number of work days varies by task, project type, and each task has a separate calendar to specify which days qualify as work days. This calendar is custom, with the underlying table listing only non-work days with fields to specify if the event applies to each calendar.

The main complication here is that each event is dependent on the previous event and these event's actual dates are input, so the form must recalculate based on the last even entered. For example:

Event A takes place before B takes place before C. If A is 1/1/01, then B is estimated to be 1/11/01 and C is estimated to be 1/16/01. If B's actual date is filled out to be 1/12/01, then C needs to recalculate to estimate 1/17/01.

Since there are 26 fields that may or may not be populated, four calendars, and four project types, I actually get 416 possible scenarios. I am concerned about how to calculate work days and how to code for so many possible scenarios in a neat fashion. It seems daunting right now.

 

[namliam]

Search the forum for "workdays" function, this actualy does the reverse of what you want to do, but you can probably addapt it.

Post back here if you run into trouble.

 

[Atomic Shrimp]

26 boxes? There's a normalization problem here - there probably shouldn't be 26 separate boxes for possible events, but rather, a child table in which there are up to 26 rows, each describing one event and all relating back to their appropriate parent records in the main table.

 

[RossWindows]

I agree with AtomicShrimp, but I also understand that your scenario is unique and thusly requires a unique and creative solution.
I'll play around with the idea today and let you know.
Is it possible to remove the confidential data and post your db?

It almost sounds like something from Microsoft Project doesn't it?

 

[Chris1112]

I've attached my database. It is a new database, so there is only a very small bit of sample data for testing. I've placed a calendar function in there, but don't have it working yet.

I appreciate the help very much.

Edit: The form is frmCTEntry. There are a series of four text boxes beneath each entered date that will become calculated fields. I've made my first attempt at calling the calendar function in the first field.