I have two fields in a table that contains date and week#. Currently, I filtered manually the date using "between" the start date and end of the current week date.
For example.
start date: 1/1/2010
Last day of the week would be 9/25/2011 since 10/1/2011 and 10/2/2011 have not arrive.
Week# DateField
Criteria: between 1/1/2010 and 9/25/2011.
This is not very practical since I have to change it very week. The reason I do this is I don't want the data shows on the week that have not yet past. In this example if I didn't specify the stop date (9/25/2011) then I will show the week 39 data(from 9/26 to 10/2/2011) which is incomplete due to two more day have not come.I want it to check it automatically in which if the current week have not past then stop at the last day of previous week.
I've searched the net but didn't find one that allow me to do what I need most just shows the current week such as DatePart, DateAdd.
Please advise