Access 2007 if then else statements

[Lorlai]

I am trying to create an if then statement in access, but have more than two conditions I want returned. Below is my pseudo code:

IF Product_Date is >= 6/1/2009 and <= 5/31/2010 THEN
Product_Year = "2010"
ELSE IF Product_Date is >= 6/1/2010 and <= 5/31/2011 THEN
Product_Year = "2011"
ELSE IF Product_Date is >= 6/1/2011 and <= 5/31/2012 THEN
Product_Year = "2012"
END IF

In access, I can only create two of these conditions:

IIf([Product_Date]>=#6/1/2009# And [Product_Date]<=#05/31/2010#,"2010","2011")

How can I incorporate more conditions into this code?

Thank you!

 

[Joe4]

You need to nest your IIF statements. Here is a link that shows you how to do that: http://www.simply-access.com/Nested_IIF_Statement.html

You can nest up to 7 levels. Any more than that, and you probably should be doing it a different way anyhow (such as linking to a table of values or creating a User Defined Function).

 

[Lorlai]

Thanks Joe! That was just what I needed.

One more question: Is it possible to include some ORs in there without messing up the system?

IF Product_Start_Date is >= 6/1/2009 and <= 5/31/2010 OR Product_Finish_Date is >= 6/1/2009 and <= 5/31/2010
THEN
Product_Year = "2010"
ELSE IF Product_Start_Date is >= 6/1/2010 and <= 5/31/2011 OR Product_Finish_Date is >= 6/1/2010 and <= 5/31/2011
THEN
Product_Year = "2011"
ELSE IF Product_Start_Date is >= 6/1/2011 and <= 5/31/2012 OR Product_Finish_Date is >= 6/1/2011 and <= 5/31/2012
THEN
Product_Year = "2012"
END IF

The code I managed to get working thanks to your awesome link is:

IIf([Product_Finish_Date]>=#11/1/2010# And [Product_Finish_Date]<=#10/31/2011#,"2011",IIf([Product_Finish_Date]>=#11/1/2011# And [Product_Finish_Date]<=#10/31/2012#,"2012","2013"))

 

[Joe4]

Its pretty much just like you have done the AND part, just the parentheses will dictate the precedence of each calculation, i.e.

=IIF((condition1 AND condition2) OR (condition3 AND condition4),return this, else...)

 

[Norie]

You don't actually need all those ifs.

If I understand correctly if the month is beyond May then add a year to the year of the date.

Product_Year:Year([Product_Finish_Date])+ Iif(Month([Product_Finish_Date])>5 ,1,0)

Please check it though, I'll not be able to test it until tomorrow.

I've used similar expressions for calculating things like tax/financial year though.

 

[Lorlai]

Thank you for your help, I have incorporated both of these into my database.


I have an interesting new question that you may be able to help with: What if I am trying to replace an existing value?

My current if statement is:

Color: [Product_Color_Name]+IIf([Product_Color_Finish_Date]<=Date(),"Blue"," ")

Where the Product color may already be included the Product_Color_Name field, but may not have been updated in the extracted data to become Blue if the finish date is less than the current date. Right now, this returns the color in the field, and if the date is also less than the current date, tacks on the blue:

RedBlue.

How can I have this erase the Red so only the Blue remains? Without turning this into an update statement?

 

[Norie]

Have you tried removing the [Product_Color_Name]+ part?