Solved

Date scheduling

Posted on 2014-12-10
2
103 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
ID: 40492735
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
ID: 40494089
Perfect.  Thank you very much.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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…
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 Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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