Week Ending in Report

[kkulick]

I need the option of putting a week ending and a week starting date on a report. I want this date to automatically update itself every time you open the report up. No matter what I try I can't figure out how to do it. Please give suggestions. Thank you.

 

[ericgeil]

If you are wanting he starting and ending dates of the current week, try this:

To get Monday place this as the control source for a text box:
=DateAdd("d",(Weekday(Now())-2)*-1,Now())


To get friday use this:
=DateAdd("d",6-Weekday(Now()),Now())

Weekday (now) gives you a number (1-7) representing the current weekday. today(Wednesday) = 4.

Weekday(Now())-2 finds how many days past Monday we are.

6-Weekday(Now() finds how many days until Friday.

The only hitch is that by default, Sunday in the first day of the week. So if you ran the report on Saturday 7/15 it would give you Monday 7/10 and Friday 7/14, but if you ran it on Sunday 7/16, it would give you Monday 7/17 & Friday 7/21. If this is a problem you need to create a user-defined function so that you can adjust the firstdayofweek property as necessary. Then you can just use that function in your formulae. Let me know if you need any help with that. Hope this helps.

Eric

 

[kkulick]

Eric,
First I'd like to thank you for your help. What you sent me fixed all of my problems but one. Hopefully you can help me with this one also. I have one report that shows the whole month but has to be grouped by the week. There may be only be 2 or three different entries in a week, but I need to show each week of the month, which means there will be 4 groups on the page. Unless of course there are too many entries to fit on one page. My problem is that I need to have the week ending to be specific for each group to show what the week ending is for each of the groups. If you have an ideas on how to do this, I would greatly appreciate if you could let me know. Thanks a lot.

Kris