HELP Newbie please! 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 [DATEREVIEWASSIGNED] 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.

 

[DCrake]

Far to many to be useable. The best method would be to write a function to do this.

Public Function AFunction(AnyString As String) As String

Dim Result As String
Select Case AnyString
   Case "x"
     Result = "One"
   Case "y"
     Result = "Two"
   Case "z"
     Result = "Etc"
End Select

AFunction = Result

End Function

Then in you query you would have a column

AliasName:AFunction([FieldName])

This would then evaluate the result using the select case statement and return the correct response

David

 

[KenHigg]

a. If you format complex code a bit it can help the readability:

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

b. Just skimming trough it, it looks as though you left off the very last 'false' part - ?

c. Maybe you should consider doing this with a Select Case in a function - ? This type logic is bad juju embedded in some obscure immediate if statement because you need an easy way to make changes and you may need to re-use it elsewhere in your app.

 

[namliam]

1) HELP Newbie please!
OMG, this almost made me skip the question... what nonsence...

2)
Your nesting iif's for the save value of +60 which could easily be resolved using an IN or OR statement

3) [DATEREVIEWASSIGNED]
You are repeating thsi field about 100 times, dont you think using it only once would be easier?
[DATEREVIEWASSIGNED] + IIF...

4) Assuming your TaskType is a combobox or something, why not have the "60" or what ever value you have to add for that type added to the source of the combobox?
That way your entire iif is redundant and becomes
[DATEREVIEWASSIGNED] + [anothervalue]

5) If TaskType is not a combobox or listbox I think it might need to be one.
even if not you probably have some limited choice which should not contain typo's so a combo is highly likely IMHO to be the best option

6) Naming conventions
Forms and controls should have a prefix to denote what they are...
Like DateReviewAssigned, it has a date in it...
frmYourForm
txtTaskType (or probably cboTasktype)
etc will make your life easier

7) Yes there is a limitted number of IIFs you can nest. Not sure how many, but your probably hit it.

8)
Welcome to AWF and Good luck!

 

[KenHigg]

We 3 spent a lot of time on a 1 post newbie

 

[chhines]

Thanks guys for the quick responses. For what its worth, I use this site a lot searching Posts that help me with things similar to other's problems, thi is just the first time I've actually posted. DCrake, can you be more specific using my example for the case statement? I'm much more comfortable using the IIf in the actual control source and letting the code be generated by the program, rather than writing the code myself. Even using someone else's example, I always seem to get the actual substitution of my own stuff screwed up. Ken, I'll check to see what may be left out at the end that you are talking about. namliam, I'll answer your questions as best as I can: Many of your comments say "easily", but it isn't "easy" for someone just starting out. I take them all with a grain of salt, though. 1)I'll attempt to do what you said with my IIf statement, if I use OR, I assume you'd just use OR in the syntax as I just did there. If I have problems I'll post back. I'll also attempt to Add DATEREVIEWASSIGNED to the beginning of my IIf statement. I did think about changing the table that pulls the TASKTYPE to actually add a number column to list the "due days", then perform a calculation using the TASKTYPE + DUEDAYS, if you know what I mean. TASKTYPE is a combo box. I Know about the naming conventions thing and have done so elsewhere in the database, but I've been a bit lazy here. I suppose if you don't use the same naming convention throughout you're due for trouble!! Thanks for the responses, I'll try some of the things and if they don't work I'll come back for DCrake's SELECT case. Thanks again!

 

[namliam]

is your enter key broken or what??

I did think about changing the table that pulls the TASKTYPE to actually add a number column to list the "due days", then perform a calculation using the TASKTYPE + DUEDAYS, if you know what I mean. TASKTYPE is a combo box.

I know what you mean and quite frankly this is THE ONLY proper way to solve this !!!
Anything IIF/Select case what ever is just basicaly WRONG!

I Know about the naming conventions thing and have done so elsewhere in the database, but I've been a bit lazy here. I suppose if you don't use the same naming convention throughout you're due for trouble!!

Doomed, DOOMED I SAY !!!
Use it (naming convention) or lose it (your mind) I am serious about this !!

I'll try some of the things and if they don't work I'll come back for DCrake's SELECT case. Thanks again!

Good luck, but OH NO NO NO NO Not the select case Thats just plain wrong IMNSHO

 

[chhines]

Ok guys, what am I doing wrong? I tried the suggestion by namliam to make the due date field formula [DATEREVIEWASSIGNED] + IIF....that just kept returning a value for DUEDATE of 60 days beyond the DATEREVIEWASSIGNED. I also tried using the OR in the statement too, which cut down the coding dramatically, but still my end date ALWAYS shows as two months(60 days). I'm still using the IIf statement and believe it should work with the reduced coding. Why isn't it picking up my other choices? I've attached my new "reduced" IIf statement. I even put it back to the way I had it for each instance using [DATEREVIEWASSIGNED] + 60, etc. which is in my "reduced" attachment, but it still only comes up with 60 days from the [DATEREVIEWASSIGNED] date.

 

[Guus2005]

You might want to check your ENTER or RETURN key, it doesn't seem to be working.
It increases readability of your post when hit now and again!

Enjoy!

 

[namliam]

1) I am not replying anymore beyond this untill you FIX YOUR ENTER KEY
2) QUIT with the IIF already, it is the road to hell
3) Using OR you need to do full equations
1=1 OR 1=2 or 1=3
And not half equations
1=1 OR 2 OR 3
If you understand what I mean

 

[DCrake]

If we are being really padantic the best method would be to have a lookup table with descriptions and values and PK's and FK's then ther would be no need for nested iif's or as nailman puts it No No No No Select case statements (Don't know what's wrong with them. but hey whatever floats your boat).

I was tempted then not to press the Return key but thought better about it.

David

 

[namliam]

David,

the OP already mentioned that this is a COMBOBOX which is already fead by a table.
How would that lookup table be anything but that same source table?
The "simple" solution is to add the duedays to the combobox as a hidden column, then retrieve it from the combobox.... I think...

 

[chhines]

Thanks for the replies yesterday guys.
I initially got it working with my IIf statement.
The logic that namliam explained for the If statement made sense, which
allowed me to fix it --for the time being.
Then, my staff hit me with "we want to add 5 more types to the task table."
Thus, needless to say I ran over the limit again.
I'm going to try populating the TASKTYPE TABLE with #'s and trying to pull them out and see if that will work.
I'll post back if I need additional help.
Right now I'm leaning towards giving my Acceptance points to namliam, unless someone comes up with
something better.
Thanks again.

 

[namliam]

Glad to see you got your enter key fixed

Then, my staff hit me with "we want to add 5 more types to the task table."
Thus, needless to say I ran over the limit again.

This is exactly the reason why you shouldnt hardcode (IIF) these type of things

They get added/removed/changed "all the time" and needing to go into code will just make for a headache!

Right now I'm leaning towards giving my Acceptance points to namliam, unless someone comes up with
something better.
Thanks again.

LOL @ "Acceptance"

 

[chhines]

Ok now I'm really confused:
I've created a custom function.
I've attached the syntax.
My Due date field just displays #Name? all the time now.
It doesn't matter if you select an option from the TASKTYPE box or not.
It also has occurred to me that maybe I didn't select the correct form to
put my Function in.
There is the main form, called "Input Facility Task Reviewer", then two subforms located on it:
the "Task_Subform"(where TASKTYPE combo box is located)
and the "Review_Subform"(where DATEREVIEWASSIGNED and DATEREVIEWDUE are located).
I placed my Function in the Main form.
Also, it has also occurred to me that maybe I am not referring to my fields on the subforms in a way that the function can understand.
I also tried adding the "DueinDays" values to the review type table as a new field,
then adding them to the combo box as a hidden field,
but I wasn't sure how to pull that value for use in a calculation for the due date field without making another long IIf statement. Thanks.

 

[boblarson]

The problems are several.

1. You need to pass [DATEREVIEWASSIGNED] to the function. It doesn't magically get it from the air. You would need to revise your function header to:

Public Function CalcDueDate(TASKTYPE As String, dteDateReviewAssigned As Date) As Date

And then you can modify the rest of your function to have:

Public Function CalcDueDate(TASKTYPE As String, dteDateReviewAssigned As Date) As Date

Dim Result As Date
Select Case TASKTYPE
    Case "Alternatives Analysis", "Major 1", "New 1", "Renewal 1", _
    "Special Project", "Stream Investigation", "Transfer 1", _
    "Informal 1"
        Result = DateAdd("d", 60, dteDateReviewAssigned)
    Case "GP-12", "Noise Complaint", "Other", "Informal 2", "Major 2", _
    "New 2", "Renewal 2", "Transfer 2", "Major 3", "New 3", "Renewal 3", _
    "Transfer 3", "Informal 3"
        Result = DateAdd("d", 30, dteDateReviewAssigned)
    Case "Six Month 1", "Six Month 2", "Six Month 3"
        Result = DateAdd("d", 20, dteDateReviewAssigned)
    Case "Water Loss"
        Result = DateAdd("d", 45, dteDateReviewAssigned)
    Case "Explore"
        Result = DateAdd("d", 7, dteDateReviewAssigned)
        
CalcDueDate = Result
        
End Select
        
End Function

And then you would need to call it from your query (or other place like):

MyNewField:CalcDueDate([TASKTYPE], [DATEREVIEWASSIGNED])

 

[chhines]

Thanks bob for your reply. I'm still confused at the syntax you've given.

When you say you have to pass the field [DATEREVIEWASSIGNED]
to the function, but you give it a different name
(i.e.dteDateReviewAssigned As Date), yet at the same time you call
TASKTYPE by its real name within the function. Why?

Also, this line is a bit confusing, but let me see if I'm understanding:
Result = DateAdd("d", 60, dteDateReviewAssigned)
Is DateAdd a built in function known to access?
What does the "d" stand for?
I don't see that "declared" anywhere in this function.
Based on the way you have it setup, I'm guessing that the DateAdd function performs a calculation where "d"= the value of the Case you selected + the 60 or whatever # is next.
Then this is output to the variable dteDateReviewAssigned, which is then = to Result and is passed back out of the function as CalcDueDate.

When I call it from the control source property of my Text box [DATEREVIEWDUE] this is what I put this in the Control source property (=CalcDueDate([TASKTYPE],[DATEREVIEWASSIGNED]).
Did I call it right?

I'm thinking that because the function doesn't look for the exact value being passed from the [DATEREVIEWASSIGNED] field, but instead looks for dteDateReviewAssigned, it won't work.
I tried substituting the [DATEREVIEWASSIGNED] for your variable dteDateReviewAssigned, but no matter what I do, I still get #NAME? in the [DATEREVIEWDUE] field.

Also, are there any good sites that show function syntax and possible examples like what I am trying to do?
I feel like we're this close, but it is frustrating because it won't work. Thanks again.

 

[boblarson]

Okay, I guess I shouild have modified the other one too. Here's the function, modified:

Public Function CalcDueDate([COLOR="Red"]strTaskType As String[/COLOR], dteDateReviewAssigned As Date) As Date

Dim Result As Date
Select Case strTaskType
    Case "Alternatives Analysis", "Major 1", "New 1", "Renewal 1", _
    "Special Project", "Stream Investigation", "Transfer 1", _
    "Informal 1"
        Result = DateAdd("d", 60, dteDateReviewAssigned)
    Case "GP-12", "Noise Complaint", "Other", "Informal 2", "Major 2", _
    "New 2", "Renewal 2", "Transfer 2", "Major 3", "New 3", "Renewal 3", _
    "Transfer 3", "Informal 3"
        Result = DateAdd("d", 30, dteDateReviewAssigned)
    Case "Six Month 1", "Six Month 2", "Six Month 3"
        Result = DateAdd("d", 20, dteDateReviewAssigned)
    Case "Water Loss"
        Result = DateAdd("d", 45, dteDateReviewAssigned)
    Case "Explore"
        Result = DateAdd("d", 7, dteDateReviewAssigned)
        
CalcDueDate = Result
        
End Select
        
End Function

And then you still call it like I showed.

When I call it from the control source property of my Text box [DATEREVIEWDUE] this is what I put this in the Control source property (=CalcDueDate([TASKTYPE],[DATEREVIEWASSIGNED]).
Did I call it right?

YES, EXACTLY right. So if it isn't working it may be due to null values in each and you would need to handle those using the NZ function in passing them in.

 

[namliam]

Seriously though, why are you pursuing this function/iif path?
Use the lookup table functionality... much better / faster AND Mucho more flexible in maintenance.

 

[chhines]

Namliam. I don't know what the steps are to call it like you are saying. I've already added it all to a table and called the field for the days "DueinDays".
However, I still will have to call it in a sequence using IIf or Select case to run the calculation won't I?
I.E. I still will have to run the calculation with the same concept of , If this value, then do this, if not then do this, etc, etc, etc....
I guess I'm confused of where I'd go from there. Thanks.