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

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.
 
C

Christopher W. Douglas

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. :^)
 
G

Guest

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...
 
J

John Ortt

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
 
G

Guest

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
 
G

Gregory Paret

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...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top