Today is 5thJan05. Why does Access think it is week 2?

Hi

Today is 5thJan05. Why does Access think it is week 2? and how can i tell it that it is in fact week 1, which it actually is.

I am using this in a query to provide a list of week numbers from a drop down box; the user then selects a week and clicks on whichever report they want to see, with data valid for that week.

Thanks in advance for any help, cheers jez.

Somewhere in the bowels of Access there is a thing that defines the day that starts the week. If it is SUNDAY, then 5-Jan-05 is, indeed, the second week of 2005. (And I think SUNDAY is the default.) The first week of 2005 started and ended on Saturday, 1-Jan-05.

Oh, you wanted the first COMPLETE week in 2005? Technically, that's a different question. The way to do this (perhaps) is to find the day number of the first day of the year. Then, when you compute the week number, include that number as the parameter in the function call for the first day of the week. (Look up, e.g., DATEPART to see the third and fourth parameters of the call, which in your case are relevant to the problem at hand.)

Or you can try to compute the Julian day-of-year and integer-divide to get the week number (zero-based). I seem to recall a Julian date option in DATEPART and some of the other date-related formatting routines.

Have you tried the Format Function as namlaim suggested here?
http://www.access-programmers.co.uk/forums/showthread.php?t=78904

Yep check out that thread! Keeps it all in 1 line

hi
thanks for the answers guys, we're definately heading in the right direction.

I can live with the first couple of days of the year being week 53, but my query returns the next week (ie data for this week we're in now) as week 2...

very strange..