Solved

DateAdd

Posted on 2012-03-15
7
311 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 49

Accepted Solution

by:
Gustav Brock earned 500 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

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 49

Expert Comment

by:Gustav Brock
ID: 37729942
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

25 Experts available now in Live!

Get 1:1 Help Now