Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DateAdd

Posted on 2012-03-15
7
Medium Priority
?
376 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 51

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 51

Expert Comment

by:Gustav Brock
ID: 37729942
You are welcome!

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 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