Nested IIf Problem

joannkt

New member
Local time
Yesterday, 16:00
Joined
Oct 30, 2013
Messages
9
I am relatively new to this sort of thing and I'm trying to figure out the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.

I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.

My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]

I want to create a calculated field for [Projected Action 2] that says:

If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays

If [Type] is "B" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 5 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 5 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 5 workdays

If [Type] is "C" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 30 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 30 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 30 workdays

If [Type] is "D" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 15 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 15 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 15 workdays

I would be happy to keep using nested IIf but I keep running into problems and I thought that there is probably a better way. I'm just not familiar with VBA or anything.

HELP!:confused:

Any suggestions would be deeply appreciated. Thanks!
 
Create a Function and use that in your Query.. I got dizzy after the first few lines, although you have the If's in order, so just make sure they are valid statements..
 
The code you posted is not understandable.
Th IIF function has no THEN.
On the other hand, the IF function should have one THEN and one END IF

So, your code is not understandable.
Where start an IF ? Where is the END ?

Anyway, the answer seems to be an external function that return the appropriate result.
 
Yes, when you get that much logic, its time to move it to a function in a module. First I would make a table to hold the number of workdays to add for each Type. So far it looks like this:

TypeDays
Type, Days
A, 10
B, 5
C, 30
D, 15

Then bring that table into your query and link to the Type. Next, write the function. I will start it:

Code:
Function get_ProjectedAction2(d, a2, a1, pa1r, pa1) As Date
    ' takes Days (d) Action 2 (a2), Action 1 (a1), Projected Action 1 Revised (pa1r) and Projected Action 1 (pa1)
    ' returns date for Projected Action 2 based on those values
 
Dim ret As Date
 
IIf(IsNull(a1)=False) then ret = DateAdd("d", d, a1)
    ' if has a1 value, adds days to it and returns that value
get_ProjectedAction2 = ret
 
End Function


Paste that into a module and then use it like this in your query:

ProjectedAction2: get_ProjectedAction2([Days],[Action 2], [Action 1], [Projected Action 1 Revised], [Projected Action 1])


When you do it will return the correct date for all records with an Action 2 value and null for those that don't. Your job is to add more logic so it works with the rest of the dates as well.
 
Thank you! I am going to give this a try and see if I can't wrap my brain around it!
 
What if the number of days to add for each type changes based on the action? For example, if for Projected Action 2 Type A needs x days added, but for Projected Action 12, Type A needs y days added?
 
I would first try to logically deduce it. For example, if [Projected Action 12] was always 2*Days for every Type, I would do that. If not, I would add another column in that new table, put it there and then pass that value to the function as well.

How complex is this function going to be?
 
Well, it's pretty complex for me! ;)

I have multiple actions associated with multiple sites. Each site is one of four site types. For each site and action I want to project the next action due date based on the site type.

If the action for which I am calculating a projected date is done, then I don't need a projected date. (Null)

If it isn't done, then based on the site type (of which there are four) the projected date will be a certain number of workdays (that varies from action to action and site type to site type) past the preceding action (of which there could be up to four depending on the site type.)

That date will be x number of days past either the date that preceding action was done, is projected to be done, or is the revised date of the date it is projected to be done.

As you can see from my description above,if there are four site types and each is a certain number of days past up to four different previous actions, this can become crazy!

I was trying to write nested IIf statements but it didn't work and was a mess:

Projected IFR’s Issued:IIf([Site Type]=”RAW LAND”,IIf(Not IsNull([Final Survey Complete]),DateAdd(“w”,10,[Final Survey Complete]),IIf(Not IsNull([Projected Final Survey Complete Revised]),DateAdd(“w”,10,[Projected Final Survey Complete Revised]),IIf(Not IsNull([Projected Final Survey Complete]),DateAdd(“w”,10,[Projected Final Survey Complete]),IIf([Site Type]=”RT”,IIf(Not IsNull([LE Received]),DateAdd(“w”,5,[LE Received]),IIf(Not IsNull([Projected LE Received Revised]),DateAdd(“w”,5,[Projected Final Survey Complete Revised]),IIf(Not IsNull([Projected LE Received]),DateAdd(“w”,5,[Projected LE Received]),IIf([Site Type]=”BTS”,IIf(Not IsNull([Final Survey Complete]),DateAdd(“w”,20,[Final Survey Complete]),(IIf(Not IsNull([Projected Final Survey Complete Revised]),DateAdd(“w”,20,[Projected Final Survey Complete Revised]),IIf(Not IsNull([Projected Final Survey Complete]),DateAdd(“w”,20,[Projected Final Survey Complete]),IIf([Site Type]=”COLO”,”N/A”)))))))))))

See what I mean? I'm still not sure how I would set up a table to correspond to each projected action and site type.
 

Users who are viewing this thread

Back
Top Bottom