Multiple Iif

[Rich]

Any ideas how I can make this statement more efficient in a module?
ContractLength: IIf(IsNull([EndOfContract]),IIf(Day(Date())<Day([StartOfContract]),DateDiff("m",[StartOfContract],Date())-1,DateDiff("m",[StartOfContract],Date()))+1,IIf(Day([EndOfContract])&lt ;Day([StartOfContract]),DateDiff("m",[StartOfContract],[EndOfContract])-1,DateDiff("m",[StartOfContract],[EndOfContract]))+1)
Although it works as is I need to add another statement that if the end of contract is greater than todays date it will only calculate up to today and not straight to the end of contract.

 

[shacket]

Use a Select Case statement instead of multiple IIfs.

 

[Rich]

Which was my thought but how would I start to construct it?

 

[Fornatian]

Would this not do something like you are after...

IIf(Day(Nz([EndofContract],Date())< ;Day([StartOfContract]),DateDiff("m",[StartOfContract],Nz([EndofContract],Date())-1,DateDiff("m",[StartOfContract],Nz([EndofContract],Date()))+1)

I may be out on a limb here because I've been staring at this Iif for a while - hypnotizing..

 

[Rich]

The problem with a straight date diff "m" is that access doesn't count the actual months between two different dates correctly, I now need to add another Iif and I know I could get it to work but the multiple Iifs are obviously inneficient and a Switch function would probably be the same, so a select case statement seems the way to go, just where to start.

 

[Pat Hartman]

Nested If's are much easier to read and modify than complex IIf() functions and should always be used in preference to them whenever possible.

I added the additional condition to the first if.

If IsNull([EndOfContract])  or [EndOfContract] > Date() Then
    If Day(Date())<Day([StartOfContract]) Then
        DateDiff("m",[StartOfContract], Date())-1
    Else
        DateDiff("m",[StartOfContract],Date())+1 
    End If
Else
    If(Day([EndOfContract])< Day([StartOfContract]) Then
         DateDiff("m",[StartOfContract],[EndOfContract])-1
    Else
        DateDiff("m",[StartOfContract],[EndOfContract]))+1)
    End If
End If

[This message has been edited by Pat Hartman (edited 08-12-2001).]

 

[Rich]

Thanks Pat much easier. Did you ever find the answer to your HasContinued?

 

[Pat Hartman]

Yes, the answer is, it doesn't work that way. The HasContinued property is only set to true if the size of an individual report section expands and causes it to span multiple pages. I was trying to identify when multiple detail records caused a group to split across multiple pages so that I could add the text "-continued" to the end of the group header field.

 

[Rich]

Yes I had the same problem logic dictates that it should work, there is a bug and a work around I can't remember the link to the site but I have a zipped copy if you'd like it.