Getting Week Number from a list of dates

[Daveswanton77]

Help...

I have a table as shown below

Week | Start | End |
1 |1/4/2004 |7/4/2004 |
2 |8/4/2004 |14/4/2004 |
3 |15/4/2004|21/4/2004|
4 |22/4/2004|28/4/2004|
etc......

I need a Query / Function that would return the number for any given date i.e 11/4/2004 would be week 2 as it is between the start and end dates. This is very easy to do in Excel using the vlookup() function but I can't find a way to do it in Access

Thanks in Advance

 

[dcx693]

It's also very easy to do in Access. There are two easy ways to do this. (Note: I'll use US formatted dates in the examples, since that's how my machine is set up.)

The function method is the easiest. Use the DatePart() function with the "ww" interval to return to you the week number based on a date. For example, DatePart("ww",#4/1/2004#) will give you 14 and DatePart("ww",#4/7/2004#) will give you 15. You actually want those two dates to give you the same week number of 1. So just adjust the DatePart slightly to subtract out 13 from the week number to start at 1. Now, adjust the input dates by subtracting 4 from them.

Now, DatePart("ww",#4/1/2004#-4)-13 gives you 1, and DatePart("ww",#4/7/2004#-4)-13 also gives you 1. You'll see that if you plug in other numbers, you'll get the correct week numbers you need.

You can use that DatePart function in a query as a calculated column. Just put the formula: DatePart("ww",[your_date_field_name]-4)-13 into a blank column, being sure to replace "[your_date_field_name]" with the actual name of the field in your database.

There's another easy way to do this using queries. Let me know if you want to see that.

 

[Daveswanton77]

Thanks very much I got it to work fine

Regards
Dave