Query to determine fiscal week in a query based on date

[zebra12]

am working on an Access 2010 database.
Each record has a date stamp. I would like the query to output the date with a corresponding week number.
I would like this information to link to a table with the date at the start of the week, the date at the end of the week.
for example, the table would look like this:
ID: 1
wkBegin: 12/29/14


wkEnd: 1/4/15
ID: 2
wkBegin: 1/5/15
wkEnd: 1/11/15
When linking to the table with my dates, I have two examples of dates: 12/30/14 and 1/10/15
I would like the output to look like this
Wk: 1 Date: 12/30/14
Wk: 2 Date: 1/10/15
I know I would need to have the two tables linked in a query.
My query will pull down the fields for weeknumber, date
How would I go about doing this?

[burrina]

The trick to doing this is to determain first Week #1 of the year required. Once you have done that.... it is as easy as taking that date and adding 7 days (per week) * 14 weeks (as per your example) to Week #1 and you have your answer...
Examples:

CurWeek: DatePart("ww",Date())
CurMonth: Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ")
CurYear: DateSerial(Year(Date()),1,1)

HTH

[zebra12]

Thank you for the suggestion.

I think I am a lot closer now.

I only need the fiscal week. If I want the fiscal week to start on September 29, 2014, how do I adjust the criteria you just gave me?

[ItsMe]

The only way I have been able to accomplish what, I believe, you are trying to do, is to store dates in tables that have a corresponding integer to represent weekdays and months. You can do a similar thing using expressions in queries but the integer is only a first step to counting periods. You will need to use additional code to count periods. This is an approach I use for the reporting of FIFO and 4-4-5 inventory stuff.

[June7]

As burrina shows, DatePart function can extract a week number. Can use that value for grouping and sorting.

DatePart("ww", Date(), vbMonday, vbFirstFullWeek)

Or can calculate the Monday of the week for any given date and group/sort on that date. Review http://msdn.microsoft.com/en-us/libr...ffice.14).aspx