Find The Week Begin and End Date Based On Current Date

[jtgarrison]

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

[manxman777]

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

[jtgarrison]

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?

[Stewart Ross]

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

[manxman777]

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.