Date Formatting - By Weeks 2

[CSADataCruncher]

I'm trying to build a report that includes a chart to show each employee's name and billable hours on a weekly basis. I see that Access will create monthly, quarterly, or annual date periods, but is there a way to bring back weekly periods? I'd like to be able to add to the report each week... or perhaps I could just have it show weekly periods for 1/1/04-12/31/04 and then run the report each week.

I've been searching my training tools and the local library but can't find help.

PLEASE, can anyone out there help me?

 

[Blorf]

I do lots of reports like this, were I want a week ending date to have the total activity for that week. Our week ends on Friday, and starts new on Saturday, so I do this:

WE: [CheckDate]+7-Weekday([checkdate],7)

This takes the date of the record, and if it is not Friday, finds the next Friday and makes it that. Then I total the transactions grouped by that, and I have my week ending number.

Hope this helps.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[CSADataCruncher]

Thank you for the reply. I don't mean to sound stupid, but is [checkdate] a field in your table? I tried it and it asked me for "Check Date" so I entered a date, but that's not what it wanted... I tried entering the name of my field, but that didn't work either.

I will read some of the items in the "Write your own Microsoft: Access Reports FAQ" as well.

Thank you again.

 

[Blorf]

Sorry. Yes, Check date is my field. I cut and pasted.

Change it from [Checkdate] to your table.field name.

Ascii dumb question, get a dumb Ansi

 

[CSADataCruncher]

OK... I think it's working closer to what you said, but I still need to refine it a little. See, my table has specific dates, but I want to capture all the billable hours for say the month of January, but have it broken down in my chart by weeks (1/4-1/10, 1/11-1/17, 1/18-1/24). Do you know of a way I can do that?

 

[Blorf]

You want it to specificaly read 1/4-1/10? If so, yes, you can do that by Formatting.

How is your table set up. Date, and hours?

If so, using the format thingie above, which starts friday, not Sunday, first modify so it starts sunday, then you can do something like this:

PE1: Format([CheckDate]+7-Weekday([checkdate],7)-7,"mm/dd&quot & " - " & Format([CheckDate]+7-Weekday([checkdate],7),"mm/dd&quot

This will put in the the 1/4 - 1/10 format.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[CSADataCruncher]

OK... I finally get it... I can use the original string you gave me for the WE: I put it into one query and then created a crosstab query based on my first query with the week ending date field as my column header. This is working with one exception. For some reason my data is not calculating the same as it was before. I'm thinking this is because of what you said about setting the start date back to Sunday and I'm not sure how you mean to do that.

This is what my string (string is probably not the right word but I didn't know what to call it) looks like at the moment:

Week Ending: [JOBS].[JobDate]+4-Weekday([JOBS].[JobDate],4)

I played around with numbers and found that 4 returns a Saturday date... but is it still putting the wrong dates together?

BTW... I really appreciate all the help you've been giving me on this.

 

[Blorf]

I don't remember. Does your week End on Sunday, or does it Start on Sunday.

Chaz

Ascii dumb question, get a dumb Ansi

 

[CSADataCruncher]

My weeks go from Sunday to Saturday. So, for instance 1/4/04 to 1/10/04.

 

[Blorf]

Ok. Try this:

Week Ending: [JOBS].[JobDate]-Weekday([JOBS].[JobDate])+1

This will move all dates from Sunday through Saturday into Sunday.

ChaZ


Ascii dumb question, get a dumb Ansi

 

[CSADataCruncher]

YEA!!!! Thank you so much! Now I can have a chart. I put the entire year into the criteria field and then carried the query over to a crosstab query and it's working perfectly.

Thank you again for your help.