Iif statement ..

[Carol Frangipane]

I have the following Iif statement.

=IIf(Year([EndTerm])>Year(Date()),0,(Year(Date())-Year([EndTerm]))/[Renew])

It is mathematically working fine; however, what I need is.. when the result is LESS THAN 1; ie .33, I would like it to display 1.

I do not know how to incorporate another Iif within this statement.

I do not want to round it..as I only need this to happen when the total is LESS than 1. Pls help!! Thank you...

Carol

 

[Brianwarnock]

try

=IIf(Year([EndTerm])>Year(Date()),0,iif((Year(Date())-Year([EndTerm]))/[Renew]<1,1,(Year(Date())-Year([EndTerm]))/[Renew]))

Not sure that the( ) is correct but the system will tell you.

Brian

 

[Carol Frangipane]

Thanks a lot Brian....that worked perfectly! I really appreciate your help.
Carol

 

[Brianwarnock]

Happy to help, but then how can I resist a lady with the same name as my favourite dessert,, but I'm sure that many a young man has used that as a chat up line.
(I'm old and happily married so no intent here, )

 

[Carol Frangipane]

Need to adjust this statement...

Thank you for helping me with this nested iif. In the last part of the statement, I need the very last part of the statement rounded. Every time I put the "round" function in I get all the parentheses out of whack.
Any help is greatly appreciated!!

=IIf(Year([EndTerm])>Year(Date()),0,iif((Year(Date())-Year([EndTerm]))/[Renew]<1,1,(Year(Date())-Year([EndTerm]))/[Renew]))

 

[gemma-the-husky]

there are two access functions to round numbers, surprisingly NOT called ROUND

INT rounds down - so int (123,5) = 123

FIX rounds up - so fix(123.5) = 124

these may help


.... although reading it again, i dont think this is quite what you want

 

[Brianwarnock]

Gemma
there is a Round function, maybe it only works in VBA but unlikely, also as far as I'm aware there is only a difference between Int and Fix when handling negative numbers, but again I could be wrong, they don't round but merely remove the decimal places.

Carol
I cannot test this and don't know where you where going wrong but try
=IIf(Year([EndTerm])>Year(Date()),0,iif((Year(Date())-Year([EndTerm]))/[Renew]<1,1,Round((Year(Date())-Year([EndTerm]))/[Renew],0)))

Brian

 

[gemma-the-husky]

brian

yep you are right - wasnt one in A97 though, and I never checked afterwards

 

[Carol Frangipane]

Can anyone help w/my logic?

I re-wrote my calculations and I thought it was working until I realized that when the "RenewNo" is equal to one, the next expiration date is incorrect. If anyone can see what I can do to get the "NxtExp" field to compute the correct date (in this case it would be 1/1/2009)


Table1
Prop = TestProperty
EffDte= 1/1/2002
Term = 3 (Contract is for 3 years)
Renew=2 (After 1/1/2005 it renews automatically every 2 years)

Query1
EndTerm: DateAdd('yyyy',[Term],[EffDte])

YrsPast: DateDiff("yyyy",[EndTerm],Date())

RenewNo: Fix(IIf([YrsPast]/[Renew]<1,1,[YrsPast]/[Renew]))IIf(Date()<[EndTerm],[EndTerm],DateAdd("yyyy",([RenewNo])*[Renew],[EndTerm]))
NxtExp: IIf(Date()<[EndTerm],[EndTerm],DateAdd("yyyy",([RenewNo])*[Renew],[EndTerm]))

I thank you for any help...

 

[Brianwarnock]

First a number of points
You have some typos in your post.
I would use Int rather than Fix as they both behave the same for positive numbers but I believe that Int is more self explanatory.

Remarks

Both Int and Fix remove the fractional part of number and return the resulting integer value.

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

Fix(number) is equivalent to:

I think that when calculating the NxtExp you must use RenewNo+1
So
RenewNo: Int(IIf([YrsPast]/[Renew]<1,1,[YrsPast]/[Renew]))
NxtExp: IIf(Date()<[EndTerm],[EndTerm],DateAdd("yyyy",([RenewNo]+1)*[Renew],[EndTerm]))

I have one reservation that is when you force RenewNo =1 therefore if testing shows this to give a problem then

RenewNo: Int(IIf([YrsPast]/[Renew]<1,1,([YrsPast]/[Renew])+1)
NxtExp: IIf(Date()<[EndTerm],[EndTerm],DateAdd("yyyy",([RenewNo])*[Renew],[EndTerm]))

might solve that, for clarity you may wish to change the name to NxtRenewNo.

Brian