Query with If statement code in access not working

[KevinSlater]

Hi i have a query in access that has the following code on one field to subtract 7days from the week date (this code works ok)

Re-jig Date:IIf(([week date]>=#12/01/04# And [week date]<=#31/12/04#) Or ([week date]>#03/01/05# And [week date]<=#31/12/05#) Or [week date]>#02/01/06#,[week date]-7,[week date])

But i need to amend the code to include in addtion anything between 31/12/08 and 31/12/09 to subtract 14 days instead.

ive tried the below but this does not work, it is still only subtracting 7 days during 2009 dates rather than 14.

Access accepts the code in the query with no error messages but appears to be ignoring the second iff statement.

Re-jig Date: IIf(([week date]>=#12/01/04# And [week date]<=#31/12/04#) Or ([week date]>#03/01/05# And [week date]<=#31/12/05#) Or [week date]>#02/01/06#,[week date]-7, IIf([week date]>=#31/12/08# And [week date]<=#31/12/09#,[week date]-14,[week date]))

ive tried just the second portion in a new column which works ok but need the whole statment working within the same column

Any suggestions on how to fix this?

 

[Rabbie]

In your second IIF ([week date]>=#31/12/08# And [week date]<=#31/12/09# will also satisfy [week date] >#02/01/06# in your first IIF statement so you will never use the second IIF statement.