Solved

Showing a date range

Posted on 2013-11-25
3
160 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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now