Week Number is incorrect for January 2005 - shows as Week 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am aware how to format the date to show the week number in a query in
Access. However, my problem is for dates in January 2005. It is showing as
Week #2 for January 2nd etc when in fact it should be week 1.

Anyone have any ideas on how to get around this 'bug'? I will then be using
this query to extract information in Frontpage 2003. We have Access 2002.

I have tried to search everywhere... but to no avail as to how to rectify
this.

The user needs to be able to choose a week number in a give year and either
show all employees who worked on the project in a given week or one employee
at a time all their work for that week number. They basically type into a
timesheet what they have done for the day and their timecards are by week
number and projects are assigned as week number increments.


Thank you in advance.
 
Cheryl,

January 2nd, 2005 is a Sunday. If your weeks start on Sunday, January 2nd
would be the start of week 2.
If you are looking at the Outlook Calendar (or something similar) to check
the date, Saturday and Sunday are put together at the end of the week, which
is probably the source of your confusion. :^)
 
OOOOhhhh.. whew... thank you... Of course that was one test date I did not
enter (or think of in that manner).... Now, I have to figure out how to get
around that one... since our work week is Saturday to Friday instead of
Monday - Saturday and they might view Week 1 to be something different.... I
just added one to my week number if it was equal to a Saturday.... but I
wonder if that will work in the long run...
hmmm...
 
I seem to recall that there is a parameter in the week number query which
decides which day the default start date is and I think this is set as
sunday, by putting a ",2" just inside the brackets I think it may have the
result you wanted. I am not sure if this is only for Excel though I have to
admit.

In excel the syntax is: WEEKNUM(A1,1)

This would apply to all weeks from that point onwards though. What may be
easier is just to subtract 1 from all weeks where the year starts on the
weekend.

Hope that helps,

John
 
Thank you.. I will look into it ...

John Ortt said:
I seem to recall that there is a parameter in the week number query which
decides which day the default start date is and I think this is set as
sunday, by putting a ",2" just inside the brackets I think it may have the
result you wanted. I am not sure if this is only for Excel though I have to
admit.

In excel the syntax is: WEEKNUM(A1,1)

This would apply to all weeks from that point onwards though. What may be
easier is just to subtract 1 from all weeks where the year starts on the
weekend.

Hope that helps,

John
Click to expand...
 
Cheryl said:
OOOOhhhh.. whew... thank you... Of course that was one test date I did not
enter (or think of in that manner).... Now, I have to figure out how to get
around that one... since our work week is Saturday to Friday instead of
Monday - Saturday and they might view Week 1 to be something different.... I
just added one to my week number if it was equal to a Saturday.... but I
wonder if that will work in the long run...
Click to expand...

If your work week is always Saturday through Friday then
datepart("ww",[DateField],7) will return 1 for 1/1/05 through 1/7/05, 2 for
1/8-14/05, etc., and should work every year.

-Greg.
 
Back
Top