Coding complex IIf into VBA Help Request

[EcolabDan]

Hello Experts,

I work for Ecolab and utilize a variety of data and I am starting to manage this data with Access 2007. I have hit a roadbump in my learning around a failing immediate IF statement in a query that someone else wrote that looks like this, that I do not know how to correct:

Jan1: IIf([Stage]="A",IIf([OpenMonth1]=2,1,(IIf([OpenYear1]=11,1,0))),IIf([CloseYear1]<2011,0,IIf([CloseMonth1]<=2,0,1)))

Their goal was to have the value of 1 input for the month of January if the project was started in january of 2011 and remained active. It also must not replace the 1 with a 0 when the project stage changes from active ("A") to closed ("C") at a later date.

So you can then see what this is doing is creating a way to determine the length of time a project was open. Each additional column in the queary reads off of the January line to determine if a 1 or a 0 should go in their respective cells, and then this is all reported via a linked pivot in excel.

I have tried changing the IIf statement around but due to the multiple variables it continually fails, which is what it should be doing from what I now know about IIf statements.

So I want to write this into VBA code. IF elseif code block, but I don't know how to set the variables equal to the other query fields so that the function can pull those values in when it runs.

Can anyone point me in the right direction on this problem? It would be much appreciated.

Thank you.

 

[spikepl]

Here is how to write a function: http://www.cpearson.com/excel/writingfunctionsinvba.aspx

 

[plog]

I'm not sure what your asking:

I don't know how to set the variables equal to the other query fields so that the function can pull those values in when it runs.

Does that mean you want to write a VBA function but you don't know how to get the data from the query through to that function? If so, this is what you do:

In your query you add this field:

Jan1: YourFunctionNameHere([Stage], [OpenMonth1], [OpenYear1], [CloseYear1], [CloseMonth1])

Then for your function in VBA looks like this:

Function YourFunctionNameHere(stage, openmonth, openyear, closeyear, closemonth)

' Insert code here

End Function

Now, in the function when you want to use the value of [Stage] you use stage, when you want to use the value of [OpenMonth] you use openmonth and so on.

Was that your question?

 

[Privateer]

The basic premise is wrong. You are using numbers where you need dates. Change the table and add two new fields: ProjectStart and ProjectEnd as dates. If you really need the month number, try TheStart: Format(ProjectStart,"m-yyyy") which will get you 12-2011 as text or StartMonth: Month(ProjectStart), which will get you the number 12. I also use StartYear: Year(ProjectStart) for query criteria like all projects that start in 2011.
As you mentioned you want to know how long the project lasted so you will need to do some date arithmatic, so try Duration: DateDiff("m",[ProjectStart],[ProjectEnd]). The "m" will get you the months, replace it with a "d" and you get days. This doesn't really answer your question, but it does improve the table and remove the problem.
Privateer

 

[smig]

Jan1: IIf([Stage]="A",IIf([OpenMonth1]=2,1,(IIf([OpenYear1]=11,1,0))),IIf([CloseYear1]<2011,0,IIf([CloseMonth1]<=2,0,1)))

I suggest you start from the end:

IIf([CloseMonth1]<=2,0,1) equal:
 
If [CloseMonth1]<=2 Then
  0
Else
  1
End If

the full code will be:

If [Stage]="A" Then
  If [OpenMonth1]=2 Then
    1
  Else
    If [OpenYear1]=11 Then
      1
    Else
      0
    End If
  End If
Else
  If [CloseYear1]<2011 Then
    0
  Else
    If [CloseMonth1]<=2 Then
      0
    Else
      1
    End If
  End If
End If

 

[EcolabDan]

Experts,

Thank you. I was able to look at the date differently due to your suggestions and limit my data. Then I was able to re-write the IIf statement to capture everything I wanted.

This is solved. I know you can mark forums solved, but I am new and can't see how to add the solved condition.

Thank you for your suggestions.

PS: I'm still working on the code to write this in VBA, but that's just a learning process for me. I don't need any help with that part of it. Thank you again for your tips!

 

[Guus2005]

this forum doesn't support the "solved" condition.

please not that IIF (immediate if) are very slow in queries because they are executed for each row. So if you're wondering why your queries are slow, that's why.

Use a function instead: Faster and easier to debug.

HTH