Date Function to dynamically produce fiscal year?

**aellistechsupport** · 05-08-2014, 08:47 AM

how to change the date functions (see entire code) plus that WHERE part so that it will dynamically update the fiscal year columns JUL-JUN?

currently using this but it will produce 6/1/2013 and 6/30/2014.

Code:

Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)

1. Fiscal year is prior year July through current year June
2. HOWEVER prior year July will include prior year June in the July column "JUL", as you might see in the code.
3. Right now the codes in place works for the current fiscal year. HOWEVER when this July comes it needs to start again for the new fiscal year. Which is July 2014 - June 2015.

So right now my view looks like this from the current code:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May
Regular Member	First Family	23551	25862	27507	29278	30125	30454	30834	31167	31374	31517	31536
Regular Member	Subsequent Family	11145	12354	13071	13942	14386	14561	14754	14945	15098	15216	15230
Regular Member	Introductory	1736	2254	2687	3194	3505	3670	3944	4181	4413	4569	4592

4. But when July comes in the current year it should ALL be blank.
5. That said, how to change it also to not show May yet. In the current month it should only report from prior June data accumulating each month until prior month. In today's case report only up until May?

Full query:

Code:

SELECT dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, IIf(Sum(IIf((DatePart("m",[PaymentDate])=6 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))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS JunFROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)) AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.EndYear)>=Year(Date())))
GROUP BY dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID
ORDER BY dbo_v030mbrshp01PdMembers.MemberTypeID;

**burrina** · 05-08-2014, 09:06 AM

You should be able to modify this code to suit your needs,

Field: FYear: Year([FieldName])-IIf([FieldName]< _
DateSerial(Year([FieldName]),9,15),1,0)

Field: FMonth: (Month([FieldName])+IIf(Day([FieldName])<15,9,10)-1) _
Mod 12+1

If the fiscal year begins on 9/15 of the previous calendar year, you can modify the FYear expression as follows:
Field: FYear: Year([FieldName])-IIf([FieldName]< _
DateSerial(Year([FieldName]),9,15),1,0)+1

HTH

**aellistechsupport** · 05-08-2014, 09:42 AM

Sorry, I'm not understanding.

Is what you've provided for the dynamic cumulating monthly columns or for the criteria (WHERE clause)?

The first column (1st column, beginning of the fiscal year) is the cumulative total of last June + last July in the JUL column:

Code:

Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])=6 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)))

However this is only LAST 6 and 7 (june and july), correct?

with my last column is JUN (12th column, end of the fiscal year) is:

Code:

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

and that gives me all the totals from last June through this June, correct?

with the Criteria (WHERE) as:

Code:

 Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)

which is limiting from 6/1/2013 through 6/30/2014.

When this fiscal year comes I need these codes to work for that fiscal year without manually changing it.
And since beginning of the next fiscal year comes in the current year (fiscal year is Jul-Jun), how can these ranges handle this change when July comes around each year?

The current ranges only holds true for the "current" fiscal year for the year it's in only.

**aellistechsupport** · 05-08-2014, 09:55 PM

How about something like this?

Code:

TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate)=IIf(DateAdd("yyyy",-1,Month(Date())=6),(dbo_v030mbrshp01PdMembers.PaymentDate) Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),6,30),Null))) OR (((dbo_v030mbrshp01PdMembers.PaymentDate)=IIf(Month(Date())=6,(dbo_v030mbrshp01PdMembers.PaymentDate) Between DateSerial(Year(Date()),6,1) And DateSerial(Year(Date())+1,6,30),Null)))
GROUP BY dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
PIVOT Format([PaymentDate],"mmm") In ("Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","June");

WHERE:

Code:

=IIf(DateAdd("yyyy",-1,Month(Date())>=6),Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),6,30),Null)

OR

Code:

=IIf(Month(Date())>=6,Between DateSerial(Year(Date()),6,1) And DateSerial(Year(Date())+1,6,30),Null)

are producing Nulls

I'll try using the RunningSum in report and label it according in the Report of the crosstab query but need to make the If statement work and produce actual data.

**aellistechsupport** · 05-08-2014, 10:10 PM

I can't get this to work:

Code:

=IIf(DateAdd("yyyy",-1,Month(Date())=6),Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),6,30),Null)

it's showing nothing

I want it to mean if a year ago is June then show between a year ago 6/1 through current year 6/30, otherwise show nothing.

**aellistechsupport** · 05-08-2014, 10:27 PM

Well, the RunningSum theory in the report is not really Running Sum.

At least I'm not getting a running sum?

**aellistechsupport** · 05-13-2014, 08:40 AM

I think I got it

Code:

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

Full SQL

Code:

TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,1)) And 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
PIVOT Format([PaymentDate],"mmm") In ("Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","June");

Then for the cumulative

Code:

SELECT FixedMonthsCrosstab.MemberTypeID, FixedMonthsCrosstab.MemberGroup, FixedMonthsCrosstab.MemberType, [Jun]+[Jul] AS July, [July]+[Aug] AS August, [August]+[Sep] AS Sept, [Sept]+[Oct] AS October, [October]+[Nov] AS November, [November]+[Dec] AS December, [December]+[Jan] AS January, [January]+[Feb] AS February, [February]+[Mar] AS March, [March]+[Apr] AS April, [April]+[May] AS May1, [May1]+[June] AS June1
FROM FixedMonthsCrosstab;

Since it'll always have the fixed column labels Jul-Jun.

**aellistechsupport** · 05-14-2014, 02:02 PM

Eh, I think this is still a problem

Although this produces 13 months of data

Code:

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

I have a query that does not use the above criteria on the PaymentDate.

And I'm using this for cumulative months for 13 months of data in 12 month columns:

Code:

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, 

IIf(Sum(IIf((DatePart("m",[PaymentDate])=6 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))) AS Jul, 

IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, 

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

FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.EndYear)>=Year(Date())))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear;

I don't fully understand what I'm looking at to know what happens when the next fiscal year comes this July.

I know that
1.

Code:

 DatePart("m",[PaymentDate])=6

means June, month 6.
2. but not sure what this is doing exactly?

Code:

DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null

I think it says if PaymentDate = today?

What happens when next month, June, comes?
And when this July comes with the above code?

Fiscal Year is prior July - current June
Where the total in the month of July include prior June total.

How do I change or what do I need to change to make sure that the monthly results in the correct year's data?

**orange** · 05-14-2014, 02:50 PM

This may help you with FiscalYear. Or if you prefer watching a video, try this one.

**aellistechsupport** · 05-14-2014, 03:14 PM

?
I've seen that and basically have that with the Between Statement

The issue is, are the columns Jul-Jun able to handle changing fiscal year come July? This year, next year, year after, etc ...

Are the parameters in each month handling that change in fiscal year?

Where I can't put the Between statement for the Payment Criteria.

Because it's any paymentdate with an enddate of current year or greater grouped by the enddate year (season)

Where each month is a cumulative value.

And the current code for each month of the fiscal year is true but I only can know it's true for the current fiscal year.

What I am unsure is what happens this July?

It looks like each month is handling current fiscal year and just don't know this well enough to know what happens when this July comes? The new fiscal year.

It is all ready handling the current fiscal year. 6/2013 -6/2014.
Is it, without any changes in code, going to handle 6/2014-6/2015 this July 2014, and so on in the following years?

**orange** · 05-14-2014, 05:20 PM

Did you try testing with some sample dates to make sure it all works?

**aellistechsupport** · 05-14-2014, 05:37 PM

Yes

Since we're in May this year it's a good month to test and the prior month April:

Code:

SELECT dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, IIf(Sum(IIf((DatePart("m",[PaymentDate])=4 Or DatePart("m",[PaymentDate])=5) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[paymentdate])>=4 And 5 And DatePart("m",[paymentdate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=4 And 5 And DatePart("m",[paymentdate])<=6,1,0))) AS Jun, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])=4 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=4 And 5 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, dbo_v030mbrshp01PdMembers.PaymentDate
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4))
GROUP BY dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.PaymentDate
HAVING (((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),5,30),DateSerial(Year(Date())+1,5,30))))
ORDER BY dbo_v030mbrshp01PdMembers.PaymentDate;

April:

Code:

IIf(Sum(IIf((DatePart("m",[PaymentDate])=3 Or DatePart("m",[PaymentDate])=4) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, 
 
IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))) AS May, 
 
IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))) AS Jun, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))) AS Aug, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))) AS Dec, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, 
 
IIf(Sum(IIf(DatePart("m",[PaymentDate])=3 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar

And although my paymentdate range is correct the monthly buckets / columns is not.
not sure how to change it so they are also correct?

**aellistechsupport** · 05-14-2014, 05:52 PM

Is it possible to have more than 12 months if I use a cross-tab?
From what I know, I can only define 12 months when formatting the Column Heading on Format(Date(),"mmm") and NOT put the year "mmm/yyyy".

OR if there's a way to label the column headers with more generic .names so that it can be used in a report.

I can do this only with 12 months though.

And I need up to 15 months to report on in the fiscal year.

So basically I'm trying to get help to fix the codes I've posted for the columns OR
get help on how I can use the cross-tab for upto 15 months out and be able to use them.

it'll be static headers
these 15 stcolumn headers:
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
March
April
May1
Jun

**aellistechsupport** · 05-14-2014, 06:47 PM

I can get 13 months but don't know if there's a way to get 15 months? From Mar-Jun?

MemberTypeID	Mth-1	Mth-2	Mth-3	Mth-4	Mth-5	Mth-6	Mth-7	Mth-8	Mth-9
24	2	60	35	17	11	9	9	3
25	2	60	46	18	10	15	7	1	2
26		11	13	7	1	2	5	2
27		3	6	1	1				1
28		1	2	1
29			1
30			1
31				1

Code:

TRANSFORM Count(dbo_v030mbrshp04FOFS.MembershipNumber) AS CountOfMembershipNumber
SELECT "Friends of FS" AS MemberGroup, dbo_v030mbrshp04FOFS.MemberType, dbo_v030mbrshp04FOFS.MemberTypeID
FROM dbo_v030mbrshp04FOFS
WHERE (((dbo_v030mbrshp04FOFS.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,3,1),DateSerial(Year(Date()),3,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))) AND ((dbo_v030mbrshp04FOFS.EndYear)>=Year(Date())))
GROUP BY dbo_v030mbrshp04FOFS.MemberTypeID, "Friends of FS", dbo_v030mbrshp04FOFS.MemberType, dbo_v030mbrshp04FOFS.MemberTypeID
PIVOT "Mth" & DateDiff("m",[PaymentDate],IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,30))) In ("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");

**aellistechsupport** · 05-14-2014, 10:44 PM

OR

What should this get changed to in order to produce results for this month?

Code:

SELECT dbo_v030mbrshp01PdMembers.MemberType, 

IIf(Sum(IIf((DatePart("m",[PaymentDate])=3 Or DatePart("m",[PaymentDate])=4) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, 

IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))) AS May, 

IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))) AS Jun, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))) AS Aug, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))) AS [Dec], 

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=3 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, 

IIf(Sum(IIf(DatePart("m",[PaymentDate])=4 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr2

FROM dbo_v030mbrshp01PdMembers

WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) 

Between IIf(Month(Date())<=4,DateSerial(Year(Date())-1,4,1),DateSerial(Year(Date()),4,1)) And IIf(Month(Date())<=4,DateSerial(Year(Date()),4,30),DateSerial(Year(Date())+1,4,30))) 

AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4))

GROUP BY dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID
ORDER BY dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID;

I tried it from April, having past now and the code does not work.

MemberType	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Apr2
First Family	180	180	180	180	180	180	180	180	180	180
Introductory	208	208	208	208	208	208	208	208	208	208
Subsequent Family	142	142	142	142	142	142	142	142	142	142

should be:

MemberType

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr2

PaymentDate

First Family

143

30-Apr-14

143

First Family

37

15-May-14

37

Introductory

52

15-May-14

52

Subsequent Family

119

30-Apr-14

119

Subsequent Family

23

13-May-14

23

So, if
1. able to do more than 13 months with the cross-tab, please let me know
OR
2. able to update the monthly codes to handle when the fiscal year changes come this July

whichever way is the easiest.

Date Function to dynamically produce fiscal year?

Thread Tools

Date Function to dynamically produce fiscal year?

Similar Threads

New fiscal year dates

Fiscal Year Tables and Relationships

Fiscal Year in Access 2007

July-June Fiscal Year, Not Jan-Dec

fiscal year

Posting Permissions

MemberTypeID	Mth-1	Mth-2	Mth-3	Mth-4	Mth-5	Mth-6	Mth-7	Mth-8	Mth-9
24	2	60	35	17	11	9	9	3
25	2	60	46	18	10	15	7	1	2
26		11	13	7	1	2	5	2
27		3	6	1	1				1
28		1	2	1
29			1
30			1
31				1

MemberTypeID	Mth-1	Mth-2	Mth-3	Mth-4	Mth-5	Mth-6	Mth-7	Mth-8	Mth-9
24	2	60	35	17	11	9	9	3
25	2	60	46	18	10	15	7	1	2
26		11	13	7	1	2	5	2
27		3	6	1	1				1
28		1	2	1
29			1
30			1
31				1

MemberTypeID	Mth-1	Mth-2	Mth-3	Mth-4	Mth-5	Mth-6	Mth-7	Mth-8	Mth-9
24	2	60	35	17	11	9	9	3
25	2	60	46	18	10	15	7	1	2
26		11	13	7	1	2	5	2
27		3	6	1	1				1
28		1	2	1
29			1
30			1
31				1