Schedule, Every second Monday of the month, need help. 5

[unborn]

I am trying to create a program that will allow me to select a time frame like the title. I want to preschedule a year of "every second monday of the month" or 3rd or 1st or heck maybe a wednsday. I have tried and tried and searched for controls and writeups and you name it but i still cant find an answer. If someoen could help me out point me in the right direction maybe explain a little on how i could do this i would highly appreciate it! Im so lost!

Also if there is a good control to monitor this let me explain real quick what im doing on this subject.

I need to visit clients houses once a month for routine checkups. My base form will have a calander that "should" highlight all the days that have scheduled checkups on them. When i click them a list on the side would populate the times and clients that need to be visited. It will also have an alert system alerting me of checkups coming up, one for a week worth of work, one for today.

Now because dates might need to change it will have to delete the schedule for that client and from today and here on out reset the schedule to the new time frame ("evey 3rd thursday of the month").

That is a general idea of what the schedule would due but i just cant seem to get it to do well anything like that and even after searching for few hours i cant even think of where to begin!

Any help would be greatly appreciated, know any great tutorials, or controls, anything really ive been all over pscode.com and i cant find anything there either.

Thanks for reading!

in the begining man created code.
in the end code will create man.
clones are coming only matter of time.
examples?

http://planetsourcecode.com

 

[unborn]

Oh im also storing this in a database. Would this be best to make a table JUST for the schedule?

| AccountID | Date | Time |

So i can pull per date or just for single account?

Also doing a noting table to so i can store notes organized.

| AccountID | Date | Note |

Once again thanks for reading! I appreciate any help/suggestions!

in the begining man created code.
in the end code will create man.
clones are coming only matter of time.
examples?

http://planetsourcecode.com

 

[JohnYingling]

The 1st any day is always the 1st thru the 7th.
The 2nd any day is always the 8th thru the 14th.
The 3rd any day is always the 15th thru the 21st.
The 4th any day is always the 22nd thru the 28th.

So the 2nd monday of October 2107

' VB.Net code
Dim dte as new Date(2107,10,8)
Do While(dte.DayOfWeek <> 1)
dte = dte.AddDays(1)
Loop

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[tsdragon]

Thanks, John. Very useful tidbit of information for the next (i.e. first) time I have to do that kind of logic. Star.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[PCLewis]

I like the simplicity of JohnYingling's solution. Here is a simple function to provide the same result in all cases and it's VB/VBA code.

Public Function FindDay(day_of_week As Long, week_of_month As Long, _
              target_year As Long, target_month As Long) As Date
  ' finds the nth (week_of_month) weekday (day_of_week)
  ' in the month and year provided.
  ' e.g.
  ' format(findday(vbmonday,2,2025,8),"dd-mmm-yyyy")
  ' finds the 2nd Monday in August 2025
  '
  Dim start_of_month As Date
  start_of_month = DateSerial(target_year, target_month, 1)
  FindDay = start_of_month + 7 * week_of_month - Weekday(start_of_month, day_of_week) + 1

End Function

 

[PCLewis]

To answer your other question about database structure, you should certainly have a table for Notes and one for Visits (assuming these are suitable nouns in your context). Try to model your tables on real life objects and I find it makes things far smoother.

In the real world you might have a diary for your scheduled visits and you might have a an indexed notebook for your useful notes (or a folder with a page for each client, stored alphabetically)

With that tidbit that John provided, and having all of your dates in a Visits table, you could concievable write a query relying on these things.

e.g.
SELECT * FROM VISITS WHERE WEEKDAY(VisitDate)=1 AND DAY(VisitDate) BETWEEN 8 and 14;

This would certainly be faster than figuring out which records to delete individually.

If you take this a step further and had a table of days (sounds weird I know - but it can be useful) then you can even use the same sort of query to add your records.

Let's say you have a table of dates (CalendarDays) and in this table you have the date (of course) and some other information that you might like to know about a date. I often have such a table for storing important dates (public holidays for example).

You have about 365 records per year which is not many at all.
But now you have some SQL that can make life easy..

e.g.
INSERT INTO Visits ( VisitDate, Reason, AccountID )
SELECT CalendarDays.CalendarDay, 'Visit Reason goes here', 123
FROM CalendarDays
WHERE Weekday(CalendarDay)=1
AND Day(CalendarDay) Between 8 And 14
AND CalendarDay > Now;

In that one statement, we have added an entry into the Visits table for every occurence of a 2nd Monday for as far into the future as our CalendarDays table extends. I am sure you will agree that this will solve your problem quite nicely?

Cheers

 

[unborn]

Hmm my reply is gone? Oh well Thanks alot to both of you!!

Running in circles is what I do best!

 

[unborn]

Lewis thanks alot this thread has become a reference thread for me now great job!.

Running in circles is what I do best!