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.

 

[Rich]

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

 

[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.