It appears you have an old copy.
how weird.
it should be just two tables.
It should have been " MembershipStats.zip"
It appears you have an old copy.
how weird.
it should be just two tables.
It should have been " MembershipStats.zip"
is it now in 2013 format? I can't read it now.
Ok, try now.
it was 2007-2010
i saved it as 2003
and i can still open it
nope still can't open it will have to wait until monday when I can get to a 2013 machine.
this is the criteria in your query 01_RegMembers:
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.Code:Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1)
If you want the query to reflect payment dates from 7/1/2013 through 6/30/2014 your statement would be:
Code:Between DateAdd("yyyy",-1,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),6,30)
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.
if you are covering 13 months then you have to make a different column to separate the two junes
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:Code: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)))
for the current year and use <> for the prior year.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)))
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.
Code:Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1)
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?
I'm not sure what's going on?Code: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)))
The old code
shows totals for July.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)))
As I said in my post
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.Code:Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1)
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.
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.
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
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)Code: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)))
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
and the 2nd column [Aug] is: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)))
and all the other subsequent reporting months [Sep]-[Jun]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)))
with the last column [Jun], current year:
WHERE:Code: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)))
Code:Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)
How about this?
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?Code: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)))
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?
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,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0)))
This is the first part of your statement. You are only checking june.Code:Jul: IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,
it should be something more like:
Where you are checking to see if the month is 6 or 7 and the year is the PREVIOUS 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,
The formula within the FALSE part of your immediate if would follow the same formula, checking june and july of the PREVIOUS year.