Solved

DateAdd

Posted on 2012-03-15
7
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 50

Expert Comment

by:Gustav Brock
ID: 37729942
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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