Create Schedule of dates

[skizzly]

Hello all,

I have a the following vba code for creating and populating a table:

    strCreateTableSQL = "CREATE TABLE WatchPatternDates "
    strCreateTableSQL = strCreateTableSQL & "(WatchDate Date NOT NULL PRIMARY KEY, "
    strCreateTableSQL = strCreateTableSQL & "Morning CHAR NOT NULL, "
    strCreateTableSQL = strCreateTableSQL & "Afternoon CHAR NOT NULL, "
    strCreateTableSQL = strCreateTableSQL & "Night CHAR NOT NULL);"

    DoCmd.RunSQL strCreateTableSQL

and

strDateInserts = "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(01/01/2015,'Blue','Green','Amber')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(02/01/2015,'Blue','Green','Amber')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(03/01/2015,'Red','Blue','Green')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(04/01/2015,'Red','Blue','Green')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(05/01/2015,'White','Red','Blue')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(06/01/2015,'White','Red','Blue')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(07/01/2015,'Amber','White','Red')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(08/01/2015,'Amber','White','Red')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(09/01/2015,'Green','Amber','White')"
            strDateInserts = strDateInserts & "INSERT INTO WatchPatternDates (WatchDate,Morning,Afternoon,Night)Values(10/01/2015,'Green','Amber','White')"
'

What i need to do is to be able create the pattern above, without hard coding the dates and color patterns.

The pattern is: each color must do two consecutive morning, afternoon and nights over a 10 day cycle, which then repeats.

The vba should also be able to take a date, using this pattern, and re-create the pattern from any date in the future or past.

Not got an idea of where to start and hope you experts can assist.

Any help with this would be greatly appreciated


:banghead:

 

[Galaxiom]

Firstly you won't get the date you want with that code because the values will be interpreted as arithmetic divisions unless you use appropriate delimiters. This would be as series of times spaced by about 42 seconds.

The job can be done with code loops but I wonder if you really need this information in a table. Would it be sufficient to be able to generate the pattern for any given date as required by using a function?

The function would take startdate and outputdate and return the information. The exact form of that information would depend on how you plan to use it. Return options could include a recordset or array. Alternatively it could return the value for Morning Afternoon or Night in response to a third parameter.

BTW. Learn to build up strings using continuation instead of that cumbersome iterated concatenation. It makes the code far easier to read and more efficient to process.

 

[The_Doc_Man]

To amplify Galaxiom's comments, the function you want to write might be really simple if you are willing to do a little bit of math ahead of time.

First, know this: An Access date is actually what is called a "cast" (or "typecast") of a DOUBLE (floating) variable. If you use FIX(Now()), you will get an integer that is the number of whole days elapsed since the system reference date, which is either 31-Dec-1899 or 1-Jan-1900, I forget exactly which one - and it doesn't matter.

What you can do is ON PAPER lay out your calendar color code that repeats on a given pattern of days. For instance, the first two days of the pattern are based on Blue, Green, and Amber. The next two days are Red, Blue, Green, ... and so on. You will see that days 1 and 2 have one color, days 3 and 4 have another color, etc. etc.

Now pick a start date for that pattern. Compute the FIX(Now()) value to give you the integer for today and then compute the FIX(#ref-date#) to get the reference integer. (The numbers can be in LONG variables.) Subtract the reference from today's date. Now take the modulus of the difference with respect to your pattern span in days. I.e. your pattern will repeat once every 10 days. The modulus would then be a number from 0 to 9for your computation.

Now a CASE statement will take care of imposing the pattern colors.