IIf statement not working, SELECT case instead?

[chhines]

I have a very long IIf statement. I think maybe I've reached the limit of how many choices you can have in the control source "Build" statement of a text box on a form. Really, the IIF statement is very confusing looking at it, so I suppose there is a better way, but I don't know how. When I try to add additional choices, nothing happens, I save, get no errors, but the new choices don't work! I am trying to calculate a due date [DATEREVIEWDUE]text field on a subform based off of the value chosen in another subform on the same main form using a field called [TASKTYPE]. Whatever value is chosen in TASKTYPE (dropdown box) it is compared with the value in the [DATEREVIEWASSIG NED] text box which calculates the DATEREVIEWDUE. It works for many instances, but when I tried to add additional options beyond 14 choices, access doesn't save the changes. It will only allow me to change the existing entries. I've attached the sample code of my current IIf statement that works. Thanks in advance.

** Edit ** Added text and removed attachment (as the attachment requires downloading which makes it a little hard to see normally).

=IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Alternatives Analysis",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Explore",[DATEREVIEWASSIGNED]+7,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="GP-12",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Informal",[DATEREVIEWASSIGNED]+45,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Major 1",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="New 1",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Noise Complaint",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Other",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Renewal",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Revision",[DATEREVIEWASSIGNED]+90,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Special Project",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Stream Investigation",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Transfer",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Water Loss",[DATEREVIEWASSIGNED]+45))))))))))))))

[ADezii]

If [DATEREVIEWASSIG NED] will always Default to the Current Date, then you can simply set its Default Value = Date(), which it currently is, and set the Locked Property of this Field to Yes.

[NeoPa]

There seem to be two issues with this question :

1. Why are you storing against a task when the item is so obviously project related?
   My advice is to take them out of your Task table and put them in the Project table instead.
2. If you are convinced that you need this facility (in spite of advice to the contrary) then you need to clear up exactly what you want. If it's simply to ensure any date already entered in a record is not changed once it's been saved, then you need a procedure, called from the Current & AfterUpdate event procedures of your form, that sets the .Locked property of the control equal to the boolean result of IsNull(Me.Contr olName).
   If, on the other hand, you want the date for each task to match any that's already been entered on any task within the project, then you're making a rod for your own back. This is one of the many reasons why the approach you seem to be using should never be employed. I don't intend to help you down that path (your path is your own choice of course) as I know you will not thank me later when you come to understand the situation more clearly.

[chhines]

Interesting replies, NeoPA and ADezii.
ADezii, you mention just locking the current text box, but won't that lock it from entering anything at all if I do that?

NeoPA, you are indicating flawed design?
If I understand what you are getting at, the main form is the Input Facility Task Reviewer, this starts out with 4 fields, a facility name, facility #, Permit # and operator.
Then there is a task subform, which is technically the same as "project", this only has a "Completed" date.
Then, the review sub-sub-form which has a review assigned, due and completion date, which is where you helped me with the calculation.
You can view all of this in the example database I uploaded earlier.
I'm not fully understanding why this would need to be changed, but if you can make a good argument and explain more, I'm willing to listen.

By looking at it now, I'm not sure that this should be moved to the top form though. Thanks.

[ADezii]

ADezii, you mention just locking the current text box, but won't that lock it from entering anything at all if I do that?

Yes. That's why I asked in Post #21 if it will 'always' Default to the Current Date.

[chhines]

Yes, it will Default to the current date, but the user can also put in their own date by entering a different date in within at least the last 30 days.

This date is entered when the task(project) is first assigned and the reviewers are chosen for each phase of the review.
This is what we are actually tracking, the workload of reviews and various stages a particular review is in at any point in time. For the overall task(project), the only date we are worried about is actual completion.

[NeoPa]

@ADezii
@ADezii,
I think you may be confusing Default to & Set to. The default should be set one way but the operator always has the option of changing it before submission.

@Chhines
There may not be flawed design. There is definately flawed explanations of your problem. If you refer to the same items variously as tasks and projects how would you expect anyone not to be confused.

I think my last post gives you a solution that can work, even though the question wasn't clearly understood at the time. If you feel you still need further help then please respond explaining, as clearly as you can, where you're at and what you need now.

[chhines]

NeoPA, you are probably correct, I'm not explaining it properly. My bad.
Anyway, once the DATEREVIEWASSIG NED is set by the first selection of it with a TASKTYPE and at the same time TASK #, which happens during our calculation of the DATEREVIEWDUE = DATEREVIEWASSIG NED + TASKTYPE select case statement from ADezii.

This DATEREVIEWASSIG NED should NOT change for the rest of that particular task and should be the same for all reviews done under that, i.e. each individual assigned to this task, which can be from 1-5 people.

I believe I will need some further explanation of how to set this up with the CURRENT and AFTERUPDATE events. I know what AFTERUPDATE does, but what syntax would I use to set this up with the CURRENT and AFTERUPDATE events?

[NeoPa]

@chhines
Instead of :

Private Sub Control_AfterUpdate()
    'Your code here
End Sub

You would have :

Private Sub Control_AfterUpdate()
    Call NewRoutine()
End Sub
 
Private Sub Form_Current()
    Call NewRoutine()
End Sub
 
Private Sub NewRoutine()
    'Your code here
End Sub

Does that answer your question?

[chhines]

NeoPA, I'm not sure I understand the logic of your suggestion. I'm sure it is painful for you to have to spell it out to me like a baby, but in my original post, it says NEWBIE. I'm not sure why we are trying to assign the IsNull value to the control. It should never be Null, so I would think this is the least choice we would want to test. Is there a coding sample somewhere so I can understand what we are attempting here?:

I was going to put these routines in the Review_subform VB section.
However, there is already an AfterUpdate set in this window, i.e, the one ADezii described before:

Private Sub DATEREVIEWASSIGNED_AfterUpdate()
Dim varRet As Variant
If Not IsNull(Forms![Input Facility Task Reviewer]!Task_Subform.Form![cboTaskType]) And _
   Not IsNull(Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]) Then
     varRet = CalcDueDate()
End If
End Sub

I started with this, but I'm missing the logic. Where to from here? SORRY, I'm lost here...

Private Sub DATEREVIEWASSIGNED_AfterUpdate()
    Call New Routine
End Sub
 
Private Sub Form_Current()
    Call New Routine
End Sub
 
Private Sub New Routine()
IsNull DATEREVIEWASSIGNED.Locked = True
End Sub

Thanks again.

[NeoPa]

You quote my last post (indirectly) yet you seem to be referring back to something I posted in #22.

What about the IsNull() comment is bothering you? Are you saying that the value will never be Null because it always has a default, even before anything is entered (life could be a lot simpler if you made your meaning clearer)?

If so (you may well be right), then instead of using IsNull() you'd need something that determines if you are entering a new record, like :

Private Sub New_Routine()
    Me.DATEREVIEWASSIGNED.Locked = (Not Me.NewRecord)
End Sub

This is basically saying that it should be locked in all cases except when a new record is being entered. If that is not the logic you want then please explain this again so that I can help find the code for what you need.

NB. There cannot be a space in the name of your procedure.