434,997 Members | 2,841 Online
Need help? Post your question and get tips & solutions from a community of 434,997 IT Pros & Developers. It's quick & easy.

# Iff Statement Help

 P: 13 Hello everyone, new to this site, I just wanted to thank you for reading my question. I have a database that I built that has all the records being used by the Date Completed which is the Date of the task being completed. The thing I want to do is have the first 6 months and last 6 months by the date completed. I wrote a query that grabs the months from my field date completed, like Format([DateCompleted],'mmmm') and from their I wanted to change this to show the first 6 months or last 6 months. Like Jan - June and July - December. The thing I tried is using an Iff statement and I can't get it to work, any help would be great appreciated. Here is the iif state I wrote. Date: IIf([Month]="January" And " February" And "March" And "April" And "May" And "June","First 6 Months","Last 6 Months") BTW, I tried using "Or" instead of "And" and it didn't work. Thank you Feb 9 '10 #1

month cannot be different values at the same time so you must use OR
A month cannot be both "January" AND "February" at the same time
so if you said that in an iif it would always evaluate to false.
But a month can be "January" or "February" or "something else"

You also need to specify month for each OR
iif Month=this OR Month=this

AND and OR are boolean mathematical operations
0 or 0 = 0
1 or 0 = 1
0 and 0 = 0
1 and 0 = 0
1 and 1 = 1

Its difficult to explain because you are using words but the way you had it is like saying
iif month= 1 + 2 + 3
which equates to iif Month=6
and not iif month=1 or month=2 or month=3

This should work
Expand|Select|Wrap|Line Numbers
1. Date: IIf([Month]="January" or [Month]= "February" or [Month]="March" or [Month]="April" or [Month]="May" or [Month]= "June","First 6 Months","Last 6 Months")
2.

Alternativey you could have left the months as numbers instead of names
That way you could have said this
Expand|Select|Wrap|Line Numbers
1. Date: iif(Month < 7 ,"First 6 Months","Last 6 Months")
2.

6 Replies

 Expert 100+ P: 1,134 month cannot be different values at the same time so you must use OR A month cannot be both "January" AND "February" at the same time so if you said that in an iif it would always evaluate to false. But a month can be "January" or "February" or "something else" You also need to specify month for each OR iif Month=this OR Month=this AND and OR are boolean mathematical operations 0 or 0 = 0 1 or 0 = 1 0 and 0 = 0 1 and 0 = 0 1 and 1 = 1 Its difficult to explain because you are using words but the way you had it is like saying iif month= 1 + 2 + 3 which equates to iif Month=6 and not iif month=1 or month=2 or month=3 This should work Expand|Select|Wrap|Line Numbers Date: IIf([Month]="January" or [Month]= "February" or [Month]="March" or [Month]="April" or [Month]="May" or [Month]= "June","First 6 Months","Last 6 Months")   Alternativey you could have left the months as numbers instead of names That way you could have said this Expand|Select|Wrap|Line Numbers Date: iif(Month < 7 ,"First 6 Months","Last 6 Months")   Feb 9 '10 #2

 Expert 2.5K+ P: 3,072 When your data can hold multiple years, the year needs to be appended to the First/Last string, else calculated values will be wrong.... Nic;o) Feb 9 '10 #3

 Expert 100+ P: 1,134 good point nico........ Feb 10 '10 #4

 P: 13 Thank you Delerna, the first expression you wrote worked perfectly, the second didn't because when it got to 10, 11 and 12, it acted like they were part of the first 6 months, the other worked to perfection. Also Nico, I actually have a query after this one which grabs the year of each "Date Completed" to either the "First or Second" 6 months, so I will be able to run my reports through the years. Thank you guys, you guys were a tremendous help! Feb 10 '10 #5

 100+ P: 122 Make sure that you are comparing numbers, and not strings. The string "12" is less than the string "7" since it's comparing the first character of each. How are you generating the month value? The format function returns a string, but you can grab the integer value of the month by using Month([DateCompleted]). Feb 10 '10 #6

 P: 13 Actually, I was grabbing the month value by using Format([DateCompleted],'m') I didn't know that using Month([DateCompleted]) brought back an integer vs a string. Thanks a lot! Feb 10 '10 #7