Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DateAdd

Posted on 2012-03-15
7
Medium Priority
?
382 Views
Last Modified: 2012-06-27
Experts,

I have this form.
It is a Tasks Form.
There is a date field with control source [DueDate]

I continually need to choose Monday as the DueDate and I want to make this a little quicker.  

How can I modify this:
Me.Due_Date = Date + 1
to always default to Monday's date (3/19/2012) if I press the button?

Lets say if today is 3/15/2012 and if I click the button then the [DueDate] will change to Moday, which is 3/19/2012.  But, of course I could be on any day such as Tuesday, Wed, etc... so I cant hardcode it like I did in the above (Me.Due_Date = Date + 1)

Any ideas?  thank you
0
Comment
Question by:pdvsa
7 Comments
 

Author Comment

by:pdvsa
ID: 37726593
I wouldnt need the code to be too smart menaing that if I was already on Monday...
0
 

Author Comment

by:pdvsa
ID: 37726803
Me.Due_Date = Date + 1   is used just to add a day to the current day
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 37728358
You can use this generic function:
Public Function DateNextWeekday( _
  ByVal datDate As Date, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Returns the date of the next weekday, as spelled in vbXxxxday, following datDate.
' 2000-09-06. Cactus Data ApS.
    
  ' No special error handling.
  On Error Resume Next
  
  DateNextWeekday = DateAdd("d", 7 - (WeekDay(datDate, bytWeekday) - 1), datDate)
    
End Function

Open in new window

So:

Me.Due_Date = DateNextWeekday(Date, vbMonday)

/gustav
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 40

Expert Comment

by:als315
ID: 37728789
No points :)
Set default value for this field to Gustav's function and you will need no code in events
0
 
LVL 28

Expert Comment

by:burrcm
ID: 37728818
You can build it in a query. Fields -

Adate:#your date# - or a lookup wherever

Weekday: Weekday([Adate],2)

Monday: DateAdd("d",-[Weekday]+1,[Adate])

Sunday: DateAdd("d",-[Weekday]+7,[Adate])

Prev Sunday: DateAdd("d",-[Weekday],[Adate])

Chris B
0
 

Author Closing Comment

by:pdvsa
ID: 37729892
very nice.  That worked perfectly.  

thank you for the expert help!
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37729942
You are welcome!

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

783 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