469,326 Members | 1,351 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Find The Week Begin and End Date Based On Current Date

I apologize in advance if this has been asked for or if I'm missing somithing obvious, but I have a form that I want to auto populate the week beginning and week ending date based on the current week. I'd also like to be able to selcect (via Option Group) last week and it give me the week begin and end date.

Thanks in advance.

Jeff
Aug 18 '10 #1
4 44793
to get you started, if Sunday is the first day of the week, here are formulae that can be used in queries or VBA or even as Default values in a form's text boxes, etc.

start of week = date()-weekday(date())+1
end of week = date()-weekday(date())+7

date() = today's date
weekday(date()) = the day of the week of today
Aug 18 '10 #2
Stupid question - this will give me the beginning of the the current week, but how do I get the previous week's beginning and end?
Aug 19 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
To go back one week subtract 7 from the value returned. Using Manxman's post as an example:

start of last week = date()-weekday(date())-6
end of last week = date()-weekday(date())

Note that these refer to Sunday as the first day of the week. To make it Monday use an optional parameter in the Weekday function which has the value of 2 for Monday as the first day of the week:

start of week = date()-weekday(date(), 2)+1
end of week = date()-weekday(date(), 2)+7
start of last week = date()-weekday(date(), 2)-6
end of last week = date()-weekday(date(), 2)

-Stewart
Aug 19 '10 #4
Jeff

As Stewart pointed out, just subtract another 7 days for the beginning of the previous week.

We all start at the beginning. I always tell people there are no stupid questions when you are a beginner.

However, when you receive an answer, stop and try to analyze it. Do not blindly copy it. Figure it out. If you do, your knowledge will grow faster.

Why does "start_of_week = date()-weekday(date())+1" work?

If you understand why, you will start to answer your own questions. If you do not understand why, ask us. If you do not ask, you will have to keep asking us for help, long after you should.

If it is VBA code, use Ctrl-G to enter it in and play with it to see what changes do.

Inside Access, press the Ctrl-G combo. A special screen opens. Type the following and press enter.

? date()-weekday(date())+1

What happens?

Play, and you will learn fast.
Aug 19 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by AdityaK | last post: by
1 post views Thread by ToysNTreasures | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.