Display "week 1" based on date in another field

[shamble]

Hi all,

Depending on what date is in a field called 'End date' i need to return
a result of week 1, week 2, etc etc. So for this year for example:
week 1 would return for dates: 02/01/2006 to 08/01/2006
week 2 would return for dates: 09/01/2006 to 15/01/2006

I've found a formula in excel that does exactly this so i've had a play
but can't get it to work in Access. I'm trying to do this at form
level. Heres the excel code if it helps:

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

This is how i formatted it in Access:

=INT((([End Date])-SUM(MOD(DATE(YEAR(([End Date])-MOD(([End
Date])-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

Anyone know of a way to do this? thanks in advance

 

[Pieter Wijnen]

A Sample Would be:
{Controlsource}=DSum("TheField","TheTable","Format(TheDateField,'yyyyww',0,0)=Format(Forms!TheForm!TheEndDate,'yyyyww',0,0)")

Note that I've added 0 for Use System defaults for FirstDayOfWeek &
WeekStart instead of US Defaults (for us living in other parts of the world)

HTH

Pieter