Error in Nested IIF Function in MS Access 2010

Question

I'm trying to build a nested IIF function in MS Access 2010 and I can't  seem to get it to work. The formula I've wrriten is as follows:

Status2: IIf([CurrentSuber]=0,IIf([BTD] Is Null And [STD] Is Null,'Registrant',IIf([BTD]=DateValue("1/1/1900") And DateDiff("d",[STD],Now())>14,'Expired_FT’, IIF([BTD]<>DateValue(“1/1/1900"),’Cancelled_Sub’,IIF(DateDiff(“d”,[STD],Now())<15 And [BTD]=DateValue(“1/1/1900”),'Free_Trialer',’NA’))))),'Suber')

This condition is a nested IIF funstion for anything that has a [CurrentSuber]=0 And the following IIF statements. Otherwise IIF[CurrentSuber]=1 (Or [CurrentSuber]<>0) then 'Suber'.Can anyone tell me where the error is?

Answer 1

You need to test each IIF separately first as   IIf([BTD]=DateValue("1/1/1900") And DateDiff("d",[STD],Now())>14,'Expired_FT’,    can never be FALSE   as the date Now() will always be greater than 14 days from 1/1/1900.

Answer 2

One thing jumps out at me.  The part IIf([BTD] Is Null And [STD] Is Null, will take the false part when either BTD or STD, but not both, is Null.  In that case the remaining IIf conditions don't seem to make sense.  Think about whether the AND should be an OR.

It's not critical, but it would be a little simpler if you replaced the DateValue("1/1/1900") by #1/1/1900#

It's not clear if Now() should be Date() or not.

I don't see why the last IIf needs the condition:  And [BTD]=DateValue(“1/1/1900”) because the previous IIf already checked if BTD was anything else.

Most/many people would avoid the difficulty of so many nested IIf functions, by creating a VBA function to do all that.  At least the code could then use IF statements on more than one line so it would be much easier to read.

Answer 3

The formula is not testing to see if #1/1/1900# is greater than 14 days or at least I thought. They are suppose to be two seperate conditions.....one to test if [BTD] is 1/1/1900 or not AND the other to test if [STD] (a date field that doesn't have 1/1/1900 in it) meets or doesn't meet the criteria being >14 days. Both have to be true in the same instance or it's false.