By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,997 Members | 2,841 Online
Bytes IT Community
+ Ask a Question
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

✓ answered by Delerna

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.  

Share this Question
Share on Google+
6 Replies


Delerna
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
  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.  
Feb 9 '10 #2

nico5038
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

Delerna
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

Post your reply

Sign in to post your reply or Sign up for a free account.