DSum for a month to month rolling / running totals and within a set date range?

[aellistechsupport]

It appears you have an old copy.



how weird.

it should be just two tables.

It should have been " MembershipStats.zip"

[rpeare]

is it now in 2013 format? I can't read it now.

[aellistechsupport]

Ok, try now.

it was 2007-2010

i saved it as 2003

and i can still open it

[rpeare]

nope still can't open it will have to wait until monday when I can get to a 2013 machine.

[rpeare]

this is the criteria in your query 01_RegMembers:

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

You are taking 13 months of data rather than 12 months of data, you are going from 6/1 of the year prior to the current year through 6/1 of the current year which is not your stated intent.

If you want the query to reflect payment dates from 7/1/2013 through 6/30/2014 your statement would be:

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

[aellistechsupport]

Thank you!

Will try that.

But yet, it is for 13 months where the first month, July must include prior June + July and the last month is the current year's June.

[rpeare]

if you are covering 13 months then you have to make a different column to separate the two junes

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

this is your current calculation for June. There is nothing in this formula indicating year is important so you'd have to do something more like:

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

for the current year and use <> for the prior year.

[aellistechsupport]

Ok, looks like it's working. the Last column, current year's June is showing blank now.

THANK YOU!

Is the WHERE statement correct in limiting to 13 months of data? Right now it shows in the detail from 6/1/13 through 4/28/14.

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

[aellistechsupport]

Ok, I am using the Jun and Jul: code provided in another query and I get Null for July and there's data for July 2013?

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

I'm not sure what's going on?

The old code

Jul: IIf(Sum(IIf(DatePart("m",[CreatedDate])>=6 And DatePart("m",[CreatedDate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[CreatedDate])>=6 And DatePart("m",[CreatedDate])<=7,1,0)))

shows totals for July.

[rpeare]

As I said in my post

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

Is looking from 6/1/2013 through 6/1/2014 I don't think that's what you want, but that's your call if you're looking for a 13 months span ending on june 30 of the current year you've got to modify that second date.

if you're trying to look at data from 6/1/2013 through 7/31/2014 that's different, the statement you have right now will work if and only if you never have any activity on june 1 of the current year.

what query are you trying to adapt in post 24.

[aellistechsupport]

Yup, change it to:
to capture 6/1/2013 through 6/30/2014
Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)
06_SynchroTheaterOnIce.
The table for this is not in the db. I was limited on what to make tables on due to the size limits of uploading files.

[rpeare]

so you solved it? The july calcuation is exactly the same as the previous query you are just substituting one date for another, the calculation/formula do not change, though now that I'm looking at it a bit closer you will likely have to change the 'then' part of the immediate if to include the same year check as the condition of the immediate iff

in other words

Jul: IIf(Sum(IIf(DatePart("m",[XX])>=6 And DatePart("m",[XX])<=7,1,0))=0 and datepart("yyyy", date()) = datepart("yyyy", [XX]),Null,Sum(IIf(DatePart("m",[XX])>=6 And DatePart("m",[XX])<=7 and datepart("yyyy", date()) = datepart("yyyy", [XX]),1,0)))

Note I didn't check this to make sure it worked, just to give you an idea how to change the formula when you have overlapping months within a period (june 2013 and june 2014)

[aellistechsupport]

Ok I tried the code and replaced [xx] with PaymentDate and got an error.

But YES, i need something to handle the "overlap" months from the prior year to current year.

Last June and this june
where last June + last July should report into the first column [Jul] and the last column [Jun] should be empty UNTIL June in the current year ... comes.

Where I was showing data in the last column [Jun] in the 01_RegMembers query

So from what I got first column [Jul] data is last year's Jun and last year's Jul

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

and the 2nd column [Aug] is:

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

and all the other subsequent reporting months [Sep]-[Jun]

with the last column [Jun], current year:

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

WHERE:

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

[aellistechsupport]

How about this?

Jun: IIf(Sum(IIf(DateSerial(Year([PaymentDate]),6,1),1,0))=0,Null,Sum(IIf(DateSerial(Year([PaymentDate]-1),3,1) And DateSerial(Year([PaymentDate]-1),7,31) Or DateSerial(Year([PaymentDate]),6,30),1,0)))

Where this is current year's Jun and totaling Jun (when it comes) + totals since last March through last Jul + all subsequent months since Jul?

Though it won't allow me to add the BETWEEN nor the <= and >= for the range between last March and last July.
So what is the best way to tell the year in each month's column's code?

[rpeare]

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.

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:

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.