Full Version: Calculated Field IFF statment syntax probs
masterkey
Hi All,

Maybe simple but my head has gone. whats wrong with this:

Renewalpending: IIf([lastass]>(Now()-913)<(Now()-1095),"yes","no")

It should work as follows: If the date in [lastass] is more than 2.5 years ago BUT less than 3 years ago. then "Yes" or "No"

Can't get my brain working on this pls help

R. Hicks
Try this ....

Renewalpending: IIf([lastass]>=Date()-1095,IIf([lastass]<=Date()-913,"Yes","No"),"No")

RDH
masterkey
Still not working, but thanx for trying.
jsitraining
Hi
Try
Renewalpending: IIf([lastass]>(Now()-913) And [lastass]<(Now()-1095),"yes","no")
HTH

Jim
masterkey
Thanks for all your help. in the end i cheated!

Renewalpending: IIf([Overdue]="No" And [lastass]<(Now()-913),"Yes","No")

:-)
r_cubed
Are you SURE ( ???? ) that you entered Ricky's example correctly, because IT WORKS !!!! (as you would expect - from Ricky)

Where are you doing this "IiF" statement.

If in a query, then here is the SQL generated from a query using Rickys example code:

SELECT Table1.Lastass, IIf([lastass]>=Date()-1095,IIf([lastass]<=Date()-913,"Yes","No"),"No") AS RenewalPending
FROM Table1;

If you were using it as the ControlSource of a form, then here is how it would/should look like in the form property:

=IIf([lastass]>=Date()-1095,IIf([lastass]<=Date()-913,"Yes","No"),"No")

In BOTH cases, the data type for LastAss was defined as "ShortDate"
R. Hicks
Thanks for the testing and confirming the result Rob ...

I had also tested what I replied and it worked on my machine .... at least as I intpreted what was asked in the original post.

RDH
masterkey
Hi Rob and Ricky,

I Put my Hands up! I am Wrong it works like a DREAM!! I have no idea why it did'nt work before maybe beacuse I keep trying to input C like code!

Sorry Again for being such a grade one muppett!

R. Hicks
No problem ... You are Welcome ...

Again ... thanks to Rob (r_cubed) for confirming the result ...

RDH