If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query question
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? -- Don C |
#2
|
|||
|
|||
Query question
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. |
#3
|
|||
|
|||
Query question
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 "Fred" wrote: 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. |
#4
|
|||
|
|||
Query question
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 -- Don C "Fred" wrote: 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. |
#5
|
|||
|
|||
Query question
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 '================================================= === Don C wrote: 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 |
Thread Tools | |
Display Modes | |
|
|