Query question

[Don C]

I am a new user to Access and I can’t figure this one out. I have a report
for work completed with [WorkYear] on my query as, WorkYear: Year([Date
Worked]) and [WorkWeek] as, WorkWeek: DatePart("ww",[Date Worked],2) because
the workers work Monday thru Sunday.

On the report, I moved [WorkYear] to the left in front of [Employees Name]
and I moved [WorkWeek] to the left after [WorkYear]. I grouped [WorkYear]
and then [WorkWeek].

My report works fine except that on the work year 2008 it shows 53 weeks
because it is showing all of 2008. The workers worked from 12/28/08 to 1/4/09
which would be include in 2009 because they got paid in 2009. How can I make
my query include 12/28/08 to 1/4/09, as year 2009 and week 1 and only show 52
weeks in a year?

 

[Fred]

If your question is how to make it always show 52 weeks in a year, you are
going to have to get them to change the Gregorian calendar because years
don't have 52 weeks, they have either 52.14 weeks or 52.28 weeks.

But I think what you really want is for it to put the week intothe year in
which it ended. In that case I'd create an expression that returns the last
day of the week, and group on that.

Hope that helps a little.

 

[Don C]

Fred, Sorry, I think that I pushed the wrong button and broke the thread
so there is another Query Question on the site. I am a new user and am trying
to get use to how this all works.

Anyway, I think that what you said about putting the week into the year
in which it ended is what I want to do, but I can't figure out how the
expression is set up. Could you or someone else help by showing me the right
way to do this?
Don C

 

[Don C]

Fred, Sorry I broke the thread, but I am fairly new to this site and am
trying to figure it out. Anyway I think that what you said about putting the
week into the year in which it ended is what I need to do but I can't figure
out how to write the expression. could you or someone else show me the
expression and the right way for this application? Thanks

 

[John Spencer]

The Expression would be something like the following to get Saturday.
If you want Friday, change the 7 to 6. If I recall correctly this might
be affected by your operating system settings and what day of the week
you have set as the first day of the week. In the USofA the Sunday is
normally considered to be the first day of the week.

DateAdd("d",7-Day([Your Date Field]),[Your Date Field])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================