Solved

Showing a date range

Posted on 2013-11-25
3
161 Views
Last Modified: 2013-11-25
I would like to be able to display the dates from Monday to Friday of a particular week on a report using the system date (which will be a date that falls during that particular week, between Mon to Friday). For example, if the system date is 11/27/2013, it will show: 11/25/13 - 11/29/13. If the date is 11/26/13, it will still show: 11/25/13 - 11/29/13. If the date is 12/5, it will show: 12/2-12/6.

Is there anyway I could accomplish this?
0
Comment
Question by:geeta_m9
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39675178
<Is there anyway I could accomplish this? >

yes, but you need to create a user define function in vba

sample

Function GetWeekDate(d As Date)  
Dim monDate As Date
monDate = d + 1 - Weekday(d, 2)
GetWeekDate = monDate & "-" & DateAdd("d", 4, monDate)
End Function
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39675623
to get the date of the Monday of the current week (Mon-Fri), use:

?dateadd("d", weekday(date(), vbMonday) -1, date())

To get the Friday of the same week, use:

?dateadd("d", 5-weekday(date(), vbMonday), date())
0
 

Author Closing Comment

by:geeta_m9
ID: 39675840
Both the solutions work well, thanks.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question