Solved

Date scheduling

Posted on 2014-12-10
2
101 Views
Last Modified: 2014-12-11
I have a form with the following fields:

Date of Last Visit (date field) named "txtDateOfPreviousVisit"
Visit Start Date (date field) named "txtStartDate"
Visit Frequency (number field) named "txtVisitFrequency"
Day of Week (number field) named "txtDayOfWeek"

When the form opens I want "txtStartDate" to populate with "txtDateOfPreviousVisit" + "txtVisitFrequency""

BUT...

If "txtDayOfWeek" is for example, 3 (which is Tuesday) then the "txtStartDate" has to be a Tuesday also but never past the "txtVisitFrequency" value.

If the date doesn't land on that day of the week then it has to be the correct day of the week prior to "txtDateOfPreviousVisit" + "txtVisitFrequency".

In other words, for example, if txtDateOfPreviousVisit is 12/9/2014 and txtDayOfWeek is 3 (Tuesday), and txtVisitFrequency is 90 then txtStartDate has to be 3/3/2015, not 3/9/2014 because that is a Monday.

Very complicated I know.  I sure hope someone can help.
0
Comment
Question by:SteveL13
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
This small chunk of code will get you started. Replace the hardcoded values for lastVisit, frequency, dayOfWeek with the values from your application and give it a go:
Private Sub Form_Load()
    Dim lastVisit As Date
    Dim startDate As Date
    Dim frequency, dayOfWeek As Integer
    
    lastVisit = #10/11/2014#
    frequency = 90
    dayOfWeek = 3
    
    startDate = lastVisit + frequency
    While DatePart("w", startDate) <> dayOfWeek
        startDate = startDate - 1
    Wend
    
End Sub

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
Perfect.  Thank you very much.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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.

772 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

12 Experts available now in Live!

Get 1:1 Help Now