Group by Weeks 1

[awl]

My goal is to group dates by weeks. I have read both threads thread701-638944 “Group by Week” and thread703-664441 “Group by week….”, however I have some difficulty. In my query I have used GoDawgs’ suggestion (1st thread#): SundayOfWeek: [PTDate]-(Weekday([PTDate])-1) This works fine. However, when I use SLCHEd’s suggestion (2nd thread#): =Format$([SundayOfWeek],”ww”,0,0) to group by week in the report header of my report, the values produced are 1-52. Problem 1: since my database [PTDate] dates begin (9-6-2003), the first week # displayed is 36, (which is correct for week beginning 8/31/2003), when actually I want the first entry to reflect “Week # 1” for the beginning of my Physical Training Program. Problem 2: for the figures shown below, the [SundayOfWeek] and [PTDate] are all grouped under week #38, thus skipping the group #39-#40. I’m not sure what I’m missing to come up with the grouping below, which is not correct.

#38 [SundayOfWeek] and [PTDate]
9/14/2003 9/16/2003
9/14/2003 9/20/2003
9/14/2003 9/20/2003
9/21/2003 9/23/2003
9/21/2003 9/26/2003
9/28/2003 10/1/2003
9/28/2003 10/1/2003
9/28/2003 10/2/2003

#41 10/5/2003 10/6/2003

Possible solution: I thought if I use the results of the [SundayOfWeek] field in the query, to create a new field [ProgramWeek #] in the same query, to generate a #. Then in my report, I would group my [ProgramWeek #] by month. In the report header along side the [ProgramWeek#], I want to display the [SundayOfWeek] data. Is this feasible?

Any suggestion in what the expression in the query and report header should be, would be helpful.

 

[GoDawgs]

For problem 1 try something like this:

iif(Format$([SundayOfWeek],”ww”,0,0)>35, Format$([SundayOfWeek],”ww”,0,0)-35, 52-(35-Format$([SundayOfWeek],”ww”,0,0))

I think that'll work, I just did that quickly in my head.

As for the second problem I don't really know why that is happening...it seems like it's got something to do with the month, like it's grouping on the month first and only taking the week number from the first month record. Maybe you've got that outside of the SundayOfWeek grouping?

Hope that helps.

Kevin

 

[awl]

Thank you for responding. I did copy your format directly into the Control Source of the report header, however, 1) Syntax error (comma)appeared, so I placed = sign in front of it, 2) then Syntax error: “Missing a closing parenthesis, bracket (]), or vertical bar (/)"; so I entered a closing parenthesis. 3) the Enter Parameter Value dialog box displayed “ww”, and after pressing the OK box, #Error displayed in the field as the Report was displayed; 4) I then deleted the brackets that was automatically placed around each of the 3 “ww” as I copied the formula into the Control Source and 5) Now the Syntax error “You may have entered an operand without an operator”.

The expression I used without brackets: =IIf(Format$([SundayOfWeek],"ww”,0,0)>35,Format$([SundayOfWeek],"ww”,0,0)-35,52-(35-Format$([SundayOfWeek],"ww”,0,0)))

Thank you for your help…. Curtis

 

[GoDawgs]

Try putting it in the query that's the recordsource for the report instead of on the report itself...I just did that and it worked fine. Also, you have a weird character after the ww (all 3), that's probably just from the site, but in case it's not I thought I'd mention it. Hope that helps.

Kevin

 

[awl]

Thank you again, Kevin. 1st Bad News: Those weird characters occurred during the site transfer; the “and” quotes. This may have been the difference. At home I use Millennium Edition Microsoft Access. In the report header I changed the ending quote characters, and it produced the “Week # 01” as I requested. I placed in the Format: "Week # "00 to get the “Week # 01” format. Week # 01 was the first entry just as I wanted. However, since I did not have any activity during the 2nd week, next in line was Week # 03. And as before, Week # 04 and Week # 05 were omitted/skipped; the [SundayOfWeek] data was grouped under Week # 03.

Good News: I did as you suggested in putting your formula in the query, naming the field [Program Week#]. I then used the [Program Week#] as the grouping in the report header, and used the [SundayOfWeek] field in the report header to display the data I wanted. The only minor problem which I live with, is that Week # 02 was omitted, since there was no activity for that week. I made a note to the user of the report “If a Week # is Omitted, There Were No Training Events for That Week”. And for the problem 2 mentioned above, it got resolved: Week # 04 and Week # 05 are displayed correctly.

Future question: With the formula you presented for the query, will the sequence number continue from “1 to whatever” as long as I have the program going, or, will the number sequence change once the new year 2004 implements a new week numbering sequence?

 

[GoDawgs]

It will just start over in the future. It will hit week 52, then be week 1 again. You'd have to add a year behind the week number to get them to be separate (actually before the week number would be better so you could sort based on the year).

 

[awl]

Kevin: Thank you for help and professionalism. Curtis...