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))))))))))))))

[MikeTheBike]

@chhines
Hi

I think I would tackle this with the Choose() function, (see VB Help).

You do not say what the Row Source of the Drop List box is, but if it is a table then I would add a field name, say 'Choose' to relate it the its use (?), as in integer type. Assign a unique number to each of the 'Choices', these should be continues numbers 1 to maxchoice. I don't know if you can make this field the bound column of the ComboBox, but if not, by using the Column property you can put the selected 'Chosen' value in a hidden form control say txtChooseIndex using the AfterUpdate event.

Then you would have a calculated like this

=Choose(Forms![Input Facility Task Reviewer]!Task_Subform.Form!txtChoosIndex,[DATEREVIEWASSIGNED]+60,[DATEREVIEWASSIGNED]+7,[DATEREVIEWASSIGNED]+30, ............. etc)
 

The order of the Choice arguments in the Choose() function would have to be in line with the 'Choose' values in the table or whatever.

This will be significantly shorter than the nested IIF() functions. Not sure if there is a limit to the number of ‘choices’ thought!

Hope that makes some sort of sense.

I am sure there are many ways to do this; perhaps someone else has other suggestions.


MTB

[ADezii]

@chhines

1. Set the Control Source of the [DATEREVIEWDUE] Field equal to a Public Function within which the logic is contained.
   

   =fCalcDateReviewDue()          'Control Source

2. Function Definition.
   

   Public Function fCalcDateReviewDue() As Date
   Dim strTaskType As String
   Dim dteDateReviewWasAssigned As Date
    
   strTaskType = Forms![Input Facility Task Reviewer]!Task_Subform.Form![TASKTYPE]
   dteDateReviewWasAssigned = Forms![Input Facility Task Reviewer]!Task_Subform.Form![DATEREVIEWASASSIGNED]
    
   Select Case strTaskType
     Case "Alternatives Analysis", "Major 1", "New 1", "Renewal", "Special Project", _
          "Stream Investigation", "Transfer"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 60
     Case "Explore"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 7
     Case "GP-12", "Noise Complaint", "Other"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 30
     Case "Informal", "Water Loss"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 45
     Case "Revision"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 90
     Case Else
       fCalcDateReviewDue = #1/1/1800#
   End Select
   End Function

3. NOTE: The major assumption is made that the 2 Fields involved in the calculations are Required, and therefore cannot be NULL. If either is NULL, all bets are OFF. It is also assumed that both Fields reside on the same Sub-Form, if not make the necessary adjustment within the Function.

[NeoPa]

CHHines,

What you've managed to fit in there can be more succinctly be written as :

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

I believe the restriction is on length rather than number of items, so I suggest you have a rethink on your object names. They are far too clumsy for use in programming. Generally including spaces anywhere in an object name is unwise. If the name is not really a name but a sentence, you know you're likely to run into problems like this one.

[ADezii]

@NeoPa
Hello NeoPa. Just for curiosity, do you think that the Switch() approach is more efficient, readable, and faster than the Function/Select Case approach, especially given the tendency for Switch() to evaluate all expressions whether or not the preceding ones evaluate to True? Just curious on your thoughts on this one.

[NeoPa]

While the Switch() function evaluates all expressions before choosing when run in the VBA environment, the SQL equivalent is not so flawed (Just like the SQL versions of IIF() & Choose()). I think it would be more efficient (far less to do and no lines of code to interpret as it goes along as is the case in VBA).

I discovered a long while ago (to my chagrin as I was very inexperienced then on the SQL side - I wasn't even aware it was behind all my QueryDefs then) that any VBA procedure in a QueryDef slowed it down inexorably. The factor was quite extreme. I had to search for alternatives then to avoid such unacceptable delays. I'm pretty sure it's related to the fact that VBA is only a semi-compiled language. It doesn't compile to machine code but to some sort of interpretable half-way house. I'm convinced it would execute more quickly (IE also more efficient than) than the code, notwithstanding the efficient nature of the code.

Clearly it's more readable as it's all in one place. Understanding what a query is doing is never helped by the requirement to switch to another window to see what is happening within a procedure.

All in all, though I recognise the logic of your thoughts on this one, I would never recommend such an approach unless absolutely necessary. I know you're a big fan of all that's VBA, so sorry to be so unequivocal, but there really is no getting away from the answer as far as I see it.

PS. I just reread my previous post and realised I didn't make it clear I was addressing myself to the OP. If it came across as a critique of your post ADezii, it was certainly not intended to. I've updated it to read more clearly.

[ADezii]

@NeoPa
I never interpreted it as a critique of my code, but simply wanted your opinion on the matter. You know how much I respect it.

[chhines]

Thanks for all the replies.
Unfortunately, I still can't get it to work.
Everything I try, the [DATEREVIEWDUE] field just shows #NAME? in it.
I changed what appeared to be a couple of errors in the function call, as the [DATEREVIEWASSIG NED] text box was named [DATEREVIEWASASS IGNED] in ADezii's reply.
Also, I changed the Task subform in the string variable, because this field is actually on the Review_subform, not the Task_subform.
I think we're close, I've attached my function again to see if maybe someone sees what is wrong. I'm thinking maybe in the two string variables, maybe we aren't referring EXACTLY to the path or way it needs to call data from those two subforms in the function.
How can I be sure that this path below in bold is correct, is there somewhere I can check this?
dteDateReviewWa sAssigned = Forms![Input Facility Task Reviewer]!Review_Subform .Form![DATEREVIEWASSIG NED]
When I look at the form in Visual Basic Project window under the class objects, it has each referenced as "Form_FORMNAMEHERE" which isn't the name we used when referring to it. Thanks again.

[ADezii]

@chhines
With your Form/Sub-Form Opened:

• Press CTRL+G to Open the Debug Window
• Type in the following line, then press the Enter Key:
  

  Debug.Print Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]

• The proper Value for ![DATEREVIEWASSIG NED] should be displayed
• You're right, you are very close to a resolution to this problem.
• Did you fdorget to set the Control Source of the [DATEREVIEWDUE] Field equal to the Function, as in:
  

  =CalcDueDate()

• If the above does not solve the problem, can you Upload a copy of the Database. I'm sure that the answer is very simple.

[NeoPa]

@chhines
Have you tried what I suggested in post #4? I see no reference to it.

If you're not interested that's fine, but if I'm to help, I need to know what you've tried, so that I can direct you further. If you prefer to work exclusively with ADezii's solution then that's no problem, but it would be nice to know if so.