Page 3 of 3 FirstFirst 123
Results 31 to 44 of 44
  1. #31
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ok will try that.

    What happens when, this is for another query of different member types, they are from March, eh ...

    and tried:
    Code:
    Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])>=3 And DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And DatePart("m",[PaymentDate])<=7,[Amount],0)))
    with the subsequent months:
    Code:
    Aug: IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 7 And DatePart("m",[paymentdate])<=8,[Amount],0)))
    Except when Mar for this year comes around:
    Code:
    Mar: 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 7 Or DatePart("m",[PaymentDate])<=3,[Amount],0)))
    ?

    through Jun
    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])>=3 And 7 Or DatePart("m",[PaymentDate])<=6,[Amount],0)))
    When I look at the details it's right BUT in this summary view it is not.
    It's overstating: Jul shows total 13+3+1 = 17 BUT it's actually 15
    MemberGroup MemberType Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun MemberTypeID
    Friends of FS Friend 13 118 186 218 238 247 256 259





    24
    Friends of FS Friend - Red Level 3 18 30 33 34 49 56 57



    25
    Friends of FS Friend - White Level 1 12 25 32 33 35 40 42



    26
    Friends of FS Friend - Blue Level
    4 11 12 13 13 13




    27
    Friends of FS Friend - Pewter
    1 3 4 4 4 4




    28
    Friends of FS Friend - Bronze Level

    1 1 1 1 1




    29
    Friends of FS Friend - Silver Level

    1 1 1 1 1




    30
    Friends of FS Friend - Gold Level


    1 1 1 1




    31

    Sum the total in Jul and it's only 15 so I'm not sure why it's doing the above and saying there's 17?
    Where is the 2 extras coming from and the code is the same just in summary view vs. detail view.
    BUT Aug is correct.

    So I'm not sure what is actually wrong?
    MemberGroup MemberType Jul MemberTypeID PaymentMonth
    Friends of FS Friend 4

    24 13-05
    Friends of FS Friend 3




    24 13-03
    Friends of FS Friend 2




    24 13-04
    Friends of FS Friend 2




    24 13-07
    Friends of FS Friend - Red Level 2




    25 13-07
    Friends of FS Friend - Red Level 1




    25 13-04
    Friends of FS Friend - White Level 1




    26 13-05

    When I sum on the above, of course I get the correct numbers up UNTIL Feb.
    MemberGroup MemberType SumOfJul SumOfAug SumOfSep SumOfOct SumOfNov SumOfDec SumOfJan SumOfFeb SumOfMar SumOfApr SumOfMay SumOfJun
    Friends of FS Friend 11 118 186 218 238 247 256 3



    Friends of FS Friend - Blue Level
    4 11 12 13 13 13




    Friends of FS Friend - Bronze Level

    1 1 1 1 1




    Friends of FS Friend - Gold Level


    1 1 1 1




    Friends of FS Friend - Pewter
    1 3 4 4 4 4




    Friends of FS Friend - Red Level 3 18 30 33 34 49 56 1



    Friends of FS Friend - Silver Level

    1 1 1 1 1




    Friends of FS Friend - White Level 1 12 25 32 33 35 40 2




  2. #32
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    So tried your suggestion and it works!
    It ties out to the details!

    THANK YOU!

    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)))
    MemberGroup MemberType Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Regular Member First Family 23551 25862 27507 29278 30125 30455 30835 31168 31375 31505

    Regular Member Subsequent Family 11145 12354 13074 13945 14389 14565 14758 14949 15102 15211

    Regular Member Introductory 1736 2254 2687 3194 3505 3670 3944 4181 4413 4528


    Whereas before I was getting:
    Result:
    MemberGroup MemberType Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Regular Member First Family 23612 25925 27572 29345 30193 30523 30904 31237 31446 31557
    31557
    Regular Member Subsequent Family 11168 12378 13100 13971 14415 14592 14785 14976 15129 15230
    15230
    Regular Member Introductory 1739 2258 2691 3198 3509 3675 3949 4187 4419 4523
    4523

    Where the subsequent months look like this:
    Code:
    Aug: 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)))
    Ending with:
    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)))
    Can you help with the above?
    Friends of FS section

  3. #33
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by rpeare View Post
    Code:
    Jul: 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)))
    This is your code to try to add june + july of the previous year into your july column as it stands now. You are only checking for june dates in the first part, and your check of the payment date in the second part are both wrong.

    Code:
    Jul: IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,
    This is the first part of your statement. You are only checking june.
    it should be something more like:

    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,
    Where you are checking to see if the month is 6 or 7 and the year is the PREVIOUS year.
    The formula within the FALSE part of your immediate if would follow the same formula, checking june and july of the PREVIOUS year.
    You know I've come to realize that this is ONLY good for the current fiscal year.
    What happens when the next fiscal year comes? Which is this July.

    All of the date ranges only looks from last Jun through this Jun.
    So after this Jun it'll still look at last Jun through this Jun not the next fiscal year. 6/1/2014 - 6/30/2014.

    How can these date ranges get updated to handle it when July 2014 arrives?
    And when July 2015 arrives, etc ...?
    Dynamically without having to manually change it in the middle of each year when the next fiscal year comes around.

  4. #34
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if memory serves you have a date range criteria on the payment date, instead of having a static value put in dynamic criteria like: between [Enter the Start Date] and [Enter the End Date]

  5. #35
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by rpeare View Post
    if memory serves you have a date range criteria on the payment date, instead of having a static value put in dynamic criteria like: between [Enter the Start Date] and [Enter the End Date]
    Can't have user input fields.

    The report is to run automatically each month based on the criteria to dynamically display the results each month and as the fiscal year changes.

  6. #36
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    We also had this conversation earlier I think. You initially said it was to run each month, then you said it was to run once a year at the end of the fiscal year.

    You have set up your report(s) to work on a fiscal year basis so which is it? or is it both? If you are reporting on the most recent complete month, or, alternately, say the most recent full month (in other words if it's april 1st, you would report on anything up through march 31st). If you don't have rules to follow for a reporting timetable you're hosed. If you have rules that you can count on you can set your query/forms up to do the work for you.

  7. #37
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    both.

    it's reporting every month, say the 10th of each month, for UP TO the prior month for the fiscal year.

    1. So on 5/10 it'll run the report showing the cumulative total counts for current "fiscal year" (6/1/2013 - 6/30/2014) for each month Jul - Jun where
    Jul of last year will have 6/1/13-7/31/13 (which would have reported that on 8/10/13.

    Another words, on 5/10/14 the report will show,
    Jul - Jun with only cumulative date up to 4/30/14. Since May isn't done yet it should not show any data for May until June.

    And when July 2014 comes around it'll update the Jul - Jun with only data for June 2014 in the Jul "bucket" (column).

    Right now the way the parameters / criterion are set it'll only show last June through this June and the same BUT once it's 2015.

    Since the fiscal year if prior Jul - current Jun in any year there's going to be a problem when the current July comes, the new fiscal year starts.


    Need it to dynamically cumulate each month the month columns

    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? Meaning 6/2013 and 7/2013. It's not going to be 6/2014 and 7/2014 in August 2014?

    with my last column in 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? meaning total from 6/2013 through 6/2014? So when July 2015 comes it'll still be 6/2013 through 6/2014 NOT 6/2014 through 6/2015 in 6/2015? I need it to do that ... dynamically handle the fiscal year being in the middle of one year and ending in the following year.

    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. So in August 2014 it'll still show 2013-2014 instead of starting over and showing 6/2014-6/2015. and so on.

    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.

  8. #38
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    here's how you get the day of the month

    datepart("d", <datefield>)

    You can use that to find out if it's the 10th of the month or greater.

    in your case, if the DAY is < 10 you want your upper bound not to be the end of the fiscal year but the last day of the month 2 months ago, if the DAY is >= 10 you want your upper bound to be the last day of the previous month.

    In other words on Feb 9, you want to report as of 12/31, on Feb10, you want to report as of 1/31.

    It's all date manipulation which you've already done in several formulas using the datepart("m", <Date>) and dateserial(<yyyy>, <m>, <d>) functions.

    In other words your criteria for the BETWEEN statement would be two immediate if statements to calculate the correct starting and ending date.

    Alternately you could calculate these values on the form where you're clicking the button to run the report then use the field on the form as the criteria for the report.
    I do this all the time and just hide the fields so the user doesn't see them and is astounded by my magic.

  9. #39
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    sorry, still not getting it.

    What I really need help with is to change the existing functions to handle when the new fiscal year comes.

    There will be no user intervention as it'll all be processed automated with the Task Scheduler on a set date each month.

    Handling to process only through the prior month is set.

    The biggest thing I need help here is changing the date functions in place now for all the months and the where (the between statement) handles each fiscal year automatically.

    Right now it is only doing last year to this year (Jun to Jun).
    It will no longer report valid data for the new fiscal year starting this July 2014 and future fiscal years when July comes around.

    So changing the WHERE to could work BUT what happens in the Jul - Jun columns?
    Code:
    Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30) or Between DateAdd("yyyy",DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date())+1,6,30)


    This is only looking at from last June or July
    Code:
    DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null

    Each field for each month is so long as it is I don't know how to modify them:
    Starting with the 1st month of the fiscal year:
    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)))

  10. #40
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think perhaps you should take a step back and look at it this way, starting from the beginning of the fiscal year.

    Your stated goals are:
    1. If it is the 9th of the month or earlier, you want the report to reflect all data with a PAYMENTDATE on or prior to the last day of the month 2 months prior
    2. If it is the 10th of the month or higher, you want the report to reflect all data with a PAYMENTDATE on or prior to the last day of the previous month.

    In other words
    On Jul 9, 2014 you want the report to run through May 31, 2014
    On Jul 10, 2014 through August 9th, you want the report to run through June 30, 2014 - NOTE this would be your year end FY report
    On Aug 10, 2014 through Sep 9th, you want the report to run through Jul 31, 2014 - NOTE This is where your new FY report would take over
    On each subsequent 10th of the month a new 'month' would be 'added' to your calculations.

    So how do you get your correct starting date? Here is what I do for complex calculations like this:

    Create a table that has ONE FIELD containing relevant 'change' dates, in your case the 9th and 10th of each month for a full year cycle.
    Create a query based on this table then start working on your formula

    In your case a good starting place may be to find where the correct end date is so start with something easy like

    MonthSubtraction: iif(datepart("d", <datefield>) <=9, -1, 0)

    In other words if the if it's less than the 9th of the month I want to subtract 1 month otherwise subtract 0 months. You'll see why I do this in a moment.

    My next step would be to add this to the current date

    ActualMonth: dateadd("m", MonthSubtraction, <datefield>)

    This would give me this:

    TestDate DateSubtraction ActualMonth
    7/9/2014 -1 6/9/2014
    7/10/2014 0 7/10/2014

    The reason I'm using -1 and 0 instead of -2 and -1 is because it's far easier to find the first day of the month than the last day of the month so my next step would be to find the first day of the actualmonth

    FirstDay: dateserial(datepart("yyyy", ActualMonth), datepart("m", ActualMonth), 1)

    From here I just subtract one day to get the last day of the reporting period with:

    EndingDate: DateAdd("d",-1,[FirstDay])

    Which gives me this:

    TestDate DateSubtraction ActualMonth FirstDay EndingDate
    7/9/2014 -1 6/9/2014 6/1/2014 5/31/2014
    7/10/2014 0 7/10/2014 7/1/2014 6/30/2014

    So to concantenate these formulas into one formula you'd have this:

    EndingDateConc: DateAdd("d",-1,(DateSerial(DatePart("yyyy",(DateAdd("m",(IIf(Da tePart("d",[testdate])<=9,-1,0)),[testdate]))),DatePart("m",(DateAdd("m",(IIf(DatePart("d",[testdate])<=9,-1,0)),[testdate]))),1)))

    Where you just add the variables back in one at a time substituting in the 'builder' formulas. Once you get the formulas working the way you want you can then adapt them to your actual data to pull for the correct period.

  11. #41
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    duplicate comment>

  12. #42
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Your stated goals are:
    1. If it is the 9th of the month or earlier, you want the report to reflect all data with a PAYMENTDATE on or prior to the last day of the month 2 months prior
    2. If it is the 10th of the month or higher, you want the report to reflect all data with a PAYMENTDATE on or prior to the last day of the previous month.


    Not at all.

    The issue is, or the goal is to:
    1. dynamically show the fiscal year in any year. Fiscal year being Jun-Jun. Actually it's Jul-Jun but for the data to report on it'll be prior year June to current year June.
    2. with each month "bucket" is a cumulative sum where the 1st "bucket" is Jul to include prior Jun + prior Jul and ending with current year's Jun with a cumulative of last Jun- current Jun
    3. then when current year's July comes around it's the next fiscal year and it needs to start reporting from this Jun through next Jul and all the monthly buckets should have null values.

    So right now it's May, it should report from last June - April. I think that is solved.


    But can't seem to get this to work. I just get nothing.

    Code:
    =IIf(Month(Date())<=6,Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),6,30),Null)
    I can use Month(Date()),0) solves the the part of always getting the prior month.

    Trying to do something like this
    Code:
    =IIf(Month(Date())<=6,Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),6,30),IIf(Month(Date())>=6,Between DateSerial(Year(Date()),6,1) And DateSerial(Year(Date())+1,Month(Date()),0))
    Doesn't produce anything. When I remove the IIF part I do get results BUT it's not accurate in what it should produce:
    Code:
    Between DateSerial(Year(Date())-1,6,1) And DateSerial(Year(Date()),Month(Date()),0) Or Between DateSerial(Year(Date()),6,1) And DateSerial(Year(Date())+1,Month(Date()),0)
    Why trying to insert the IIF statements.
    NOTE: for testing I just changed the 6,1 to 4,1 since June this year isn't here yet

    MemberTypeID MemberGroup MemberType Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May June combined
    1 Regular Member First Family 14858 8737 2312 1646 1771 847 329 381 333 207 279 101
    2 Regular Member Subsequent Family 6667 4511 1217 723 873 447 181 194 191 157 211 94
    4 Regular Member Introductory 921 819 518 433 507 312 165 274 237 232 295 135
    6 Regular Member Individual First Family 582 287 132 135 96 66 45 77 60 39 40 33
    7 Regular Member Individual Subsequent Fam 73 41 13 16 7 6 4 6 7 4 7 4
    9 Basic Skills Member New Basic Skills Member 1536 3488 2358 5147 6333 5518 4242 10769 9729 11281 13069 5674
    10 Basic Skills Member New Basic Skills Instructor 67 99 82 155 121 112 50 166 127 81 79 29
    41 Basic Skills Member Renewing Basic Skills Member 1826 4464 2318 4682 5232 3638 1416 2728 3358 1548 2416 1526
    43 Basic Skills Member Renewing Basic Skills Instructor 404 544 357 559 380 257 46 54 55 21 95 100
    MemberTypeID MemberGroup MemberType Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May June current
    1 Regular Member First Family 14858 8737 2312 1646 1771 847 329 381 333 207 279 74
    2 Regular Member Subsequent Family 6667 4511 1217 723 873 447 181 194 191 157 211 76
    4 Regular Member Introductory 921 819 518 433 507 312 165 274 237 232 295 95
    6 Regular Member Individual First Family 582 287 132 135 96 66 45 77 60 39 40 22
    7 Regular Member Individual Subsequent Fam 73 41 13 16 7 6 4 6 7 4 7 4
    9 Basic Skills Member New Basic Skills Member 1536 3488 2358 5147 6333 5518 4242 10769 9729 11281 13069 4516
    10 Basic Skills Member New Basic Skills Instructor 67 99 82 155 121 112 50 166 127 81 79 21
    41 Basic Skills Member Renewing Basic Skills Member 1826 4464 2318 4682 5232 3638 1416 2728 3358 1548 2416 1412
    43 Basic Skills Member Renewing Basic Skills Instructor 404 544 357 559 380 257 46 54 55 21 95 99
    MemberTypeID MemberGroup MemberType Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May June next
    1 Regular Member First Family 143 27
    2 Regular Member Subsequent Family 119 18
    4 Regular Member Introductory 156 40
    6 Regular Member Individual First Family 20 11
    7 Regular Member Individual Subsequent Fam 4
    9 Basic Skills Member New Basic Skills Member 8321 1158
    10 Basic Skills Member New Basic Skills Instructor 58 8
    41 Basic Skills Member Renewing Basic Skills Member 1353 114
    43 Basic Skills Member Renewing Basic Skills Instructor 19 1

  13. #43
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410

  14. #44
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410

    Not working properly

    Having issues ...

    Goal:
    to show 15 months of cumulative data in 12 month columns (buckets).

    Issues:
    The 12 months is for a fiscal year.
    Fiscal year is Jul - Jun . AKA: current fiscal year is Jul 2014 through Jun 2015.
    HOWEVER the first month of Aug MUST include payments from Jun. So in reality it's Jun through Jul the following year. (Jun 2014 - Jul 2015)
    CHECK POINT: if you look at the prior year, it would be Jun 2013 - Jul 2014.
    Where Jun 2014 through Aug 2014 falls into the first month column "Aug" and "Sep" must include Jun-Sep and so on.

    The data needs to change when the query is ran in Aug.
    So until July 31, it would show May2013-Jun2014.
    But when it runs in Aug it needs to show May2014-Aug2014 and so on with the last month column Jun to show May2014-Jun2014.

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

    Full query:
    Code:
    SELECT MembershipsDetailsByDateCollegiate.MemberTypeID, Year([EndDate]) AS SeasonEnd, MembershipsDetailsByDateCollegiate.Group, MembershipsDetailsByDateCollegiate.MemberType, 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))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 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])>=5 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])>=5 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])>=5 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])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
    FROM MembershipsDetailsByDateCollegiate
    WHERE (((MembershipsDetailsByDateCollegiate.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND ((MembershipsDetailsByDateCollegiate.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
    GROUP BY MembershipsDetailsByDateCollegiate.MemberTypeID, Year([EndDate]), MembershipsDetailsByDateCollegiate.Group, MembershipsDetailsByDateCollegiate.MemberType
    ORDER BY MembershipsDetailsByDateCollegiate.MemberTypeID, Year([EndDate]);
    Here's the result.

    I don't know why the first month, Jul, which should be May 2014 - July 2014 numbers.
    Yet Aug-Jan is showing the carry over numbers (repeating them until there is data for that month to be added with the prior months) and Feb-May are blank.

    What is wrong with the column expressions?

    MemberTypeID Group MemberType SeasonEnd Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    3 Regular Membership Collegiate 2015 553 664 735 818 860 888 916 939 953 958 958 958
    3 Regular Membership Collegiate 2016 523 620 721 800 825 839 859 879 892 894 894 894
    3 Regular Membership Collegiate 2017 539 624 695 816 852 866 898 924 934 944 944 944
    3 Regular Membership Collegiate 2018
    660 660 660 660 660 660



    660
    8 Individual Membership Individual Collegiate 2015
    12 22 30 33 33 36 38
    40 40 40
    8 Individual Membership Individual Collegiate 2016 12 17 24 28 36 38 42
    45 46
    46
    8 Individual Membership Individual Collegiate 2017 20 25 35 41 47 50 56 61 64 65 65 65
    8 Individual Membership Individual Collegiate 2018
    25 25 25 25 25 25



    25

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. rolling 12 month report
    By tngirl in forum Reports
    Replies: 7
    Last Post: 03-13-2014, 01:50 PM
  2. Date Range Query Only Returns Month and Day
    By hammer187 in forum Queries
    Replies: 5
    Last Post: 09-18-2012, 11:25 AM
  3. Replies: 4
    Last Post: 05-26-2012, 09:29 AM
  4. Rolling 12 or 6 Month Query
    By Shakenaw in forum Access
    Replies: 2
    Last Post: 09-22-2011, 09:24 AM
  5. Rolling 3 Month sums
    By michaeljohnh in forum Reports
    Replies: 1
    Last Post: 11-08-2010, 05:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums