You have "And 7" in your IIf() statements for Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, and May, which will always return True (since you're not comparing the 7 to anything else). I'm guessing that's not what you want to be doing.
Also, on Jul and Jun, you have logic that compares the month and accepts two values but then immediately compares it to one value...
Example Jul breakdown:
Code:
IIf(
(DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7)
And DatePart("m",[paymentdate])<=7,
1,0
)
This equates to:
Code:
IIf(
(X >= 5 Or X =< 7) And X =< 7,
1,0
)
You do the same thing with Jun, just using months 6 and 7 instead of 5 and 7. If you're going to only accept a month of 7 as a valid answer in your IIf(), then you can just use:
Code:
IIf(DatePart("m",[paymentdate])<=7,1,0)
It's pretty late for me here, but I'll take another look at your SQL Query in the morning and see if I can come up with something that might be a little more what you want. In the meantime, are you saying that there will never be any May/June purchases except for the current Fiscal year, or for the calendar year (i.e.: If it's November, should the report still show purchases for May/June or should they all be rolled over into July by that point)?