Help with Access Formula

[Vicky Bamishile]

Why am i getting values for the 2008-08 formulae, I do have policies that incepting with 1/1/08 date, please help...thanks.. .see formula below.

Treaty_Year: IIf([Policy Incpt Date]>=#07/01/2007#,IIf([Policy Incpt Date]<=#12/31/2007#,IIf([Bord Yr]=2007,"2007-07",IIf([Policy Incpt Date]>=#07/01/2007#,IIf([Policy Incpt Date]<=#12/31/2007#,IIf([Bord Yr]=2008,"2007-08",IIf([Policy Incpt Date]>=#01/01/2008#,IIf([Policy Incpt Date]<=#06/30/2008#,IIf([Bord Yr]=2008,"2008-08","NA"))))))) ))

[orangeCat]

I don't think you are using the iif correctly.

MS Access: iif Function

In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition is the value that you want to test.

value_if_true is the value that is returned if condition evaluates to TRUE.

value_if_false is the value that is return if condition evaluates to FALSE.

For example:

iif ([Qty] > 10, "large", "small")

would return "large" if the value in the Qty field is greater than 10. Otherwise, it would return "small".

This is equivalent to the following IF statement in VBA code.

If [Qty] > 10 Then
result = "large"
Else
result = "small"
End If

Your expression:

Treaty_Year:
IIf([Policy Incpt Date]>=#07/01/2007#,
IIf([Policy Incpt Date]<=#12/31/2007#,
IIf([Bord Yr]=2007,"2007-07",
IIf([Policy Incpt Date]>=#07/01/2007#,
IIf([Policy Incpt Date]<=#12/31/2007#,
IIf([Bord Yr]=2008,"2007-08",
IIf([Policy Incpt Date]>=#01/01/2008#,
IIf([Policy Incpt Date]<=#06/30/2008#,
IIf([Bord Yr]=2008,"2008-08","NA"))))))) ))


An example of a working expression - used to create groupings based on ranges

SpendCategory:
IIf([Amount]<=10000,"Up to $10,000.00",
IIf([Amount] Between 10000.01 And 250000,"$10,000 .01 to $250,000.00",
IIf([Amount] Between 250000.01 And 1000000,"$250,0 00.01 to $1,000,000.00",
IIf([Amount] Between 1000000.01 And 5000000,"$1,000 ,000.01 to $5,000,000.00",
IIf([Amount]>5000000,"Great er than $5,000,000.00", "")))))