Iff Statement Help

[FreddieIT]

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","Fir st 6 Months","Last 6 Months")

BTW, I tried using "Or" instead of "And" and it didn't work.

Thank you

[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
[code=sql]
Date: IIf([Month]="January" or [Month]= "February" or [Month]="March" or [Month]="April" or [Month]="May" or [Month]= "June","Fir st 6 Months","Last 6 Months")
[/code]


Alternativey you could have left the months as numbers instead of names
That way you could have said this
[code=sql]
Date: iif(Month < 7 ,"First 6 Months","Last 6 Months")
[/code]

[nico5038]

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)

[Delerna]

good point nico........

[FreddieIT]

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!

[gershwyn]

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]).

[FreddieIT]

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!