Help with IIF statement please

[aellistechsupport]

I have this code

Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0)))

and the total is wrong. it should be 11,669 but it's showing over 17,646

Jul
17646

When I change the 5 to 6 it's correct in showing 11,550 HOWEVER I need it to include last May as well.

Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And DatePart("m",[PaymentDate])<=7,1,0)))

Jul
11550

I don't know this well enough to figure out what the problem is.

I know it should be 11,669 records based on looking at the details for May, Jun, and Jul

I don't know where the 6k is by changing it to 5.

NOTE: PaymentDate =

Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),Month(Date()),0),DateSerial(Year(Date())+1,Month(Date()),0))

Please help.

Thank you!

[lfpm062010]

Your SQL looks fine. The problem is in your data (my first quess). It is difficult to figure out data problem without looking at the data.

If you can provide sample data set along with you query and expected result, that will be easier to debug your issue.

[rpeare]

There's a problem with your formula by saying

DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7

you are basically including all months

for instance january (month 1), would be picked up because the datepart("m",[PaymentDate]) is less than or = 7
december would be picked up (month 12), because the month is greater than or equal to 5

if you are trying to pick up only the data between 5 and 7 you would need

DatePart("m",[PaymentDate])>=5 AND DatePart("m",[PaymentDate])<=7

[aellistechsupport]

humh,
then why does this work?

When I change the 5 to 6 it's correct in showing 11,550 HOWEVER I need it to include last May as well.
Code:
Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And DatePart("m",[PaymentDate])<=7,1,0)))

Jul
11550

I ended doing it another way but I am going to test that.

Also, the data is fine, it's the code ...

[rpeare]

The 'correctness' of your current formula may have to do with the timing, I suspect that when you produced this formula in may it worked just fine but that's a guess.

Perhaps it would be easier to ask what exactly you intend this formula to do. I had originally thought you were trying to perform a calculation on any item that fell between may and june of a given year, but looking at it again I can't really tell. Are you trying to perform a sum on any given month for a rolling year, i.e. For july, you want to sum information for records running from Aug 1 of the previous year through July 21 of the current year. If this is your goal (it doesn't really matter what period you're trying to sum over, so don't get caught up in my example) there is likely an easier way/formula to handle this.

If you can clarify what you want this formula to do it would be helpful.

[alansidman]

If you are looking to gather data between two dates, then why not use the simpler between function available in SQL.

Look at this link for a quick guide on BETWEEN

http://www.w3schools.com/sql/sql_between.asp

[aellistechsupport]

So I ended up using this instead.
Though having to do it in two steps to get the cumulative totals

TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))) AND ((dbo_v030mbrshp01PdMembers.EndDate)=IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))))
GROUP BY dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
ORDER BY dbo_v030mbrshp01PdMembers.MemberTypeID
PIVOT "Mth" & DateDiff("m",[PaymentDate],IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,30),DateSerial(Year(Date()),6,30))) In ("Mth1","Mth0","Mth-1","Mth-2","Mth-3","Mth-4","Mth-5","Mth-6","Mth-7","Mth-8","Mth-9","Mth-10","Mth-11","Mth-12");

then this for the cumulation:

SELECT [01_RegMembers].MemberTypeID, [01_RegMembers].MemberGroup, [01_RegMembers].MemberType, Nz([Mth1])+Nz([Mth0])+Nz([Mth-1]) AS Jul, [Jul]+Nz([Mth-2]) AS Aug, [Aug]+Nz([Mth-3]) AS Sep, [Sep]+Nz([Mth-4]) AS Oct, [Oct]+Nz([Mth-5]) AS Nov, [Nov]+Nz([Mth-6]) AS [Dec], [Dec]+Nz([Mth-7]) AS Jan, [Jan]+Nz([Mth-8]) AS Feb, [Feb]+Nz([Mth-9]) AS Mar, [Mar]+Nz([Mth-10]) AS Apr, [Apr]+Nz([Mth-11]) AS May, [May]+Nz([Mth-12]) AS Jun
FROM 01_RegMembers
ORDER BY [01_RegMembers].MemberTypeID;

This ties out to the details