Populating a schedule from my data and appending it to a table

[josephsimonbenn]

I have am trying to find someone who is able to help me write a code so i can calculate the 'days on' in a course of random events.... I have the Number of days in each cycle, the number of cycles required and a start date. I have to be able to populate the schedule from clicking a CmdButton.

Is this possible? Can Anyone Suggest a way of doing it any other way :)

See the attached database.

[ADezii]

Not exactly sure what you mean by the above Statement, but if you are looking for

<[Start Date]> + (<[Days in Cycle>] * <[Cycles Required])

Then for a Start Date of 2/3/2010, a 28 Day Cycle, with 4 Cycles Required, you would use the following Expression:

DateAdd("d",(28 * 4),#2/3/2010#)

which would evaluate to:

5/26/2010

Is this the logic that you are looking for?

[josephsimonbenn]

Even though there may be 'n' number of days in a cycle, i want to find the date for a specfic date in that cycle...

for example:

In any 14 day cycle, I have to phone my mum on day 2, day 11, day 14. My first phone call is on 01/01/2010. I have to repeat this cycle 3 times. So....

Cycle Day Date Dates to be Appended to new table:
1 1 01/01/2010
1 2 02/01/2010 X
1 3 03/01/2010
1 4 04/01/2010
1 5 05/01/2010
1 6 06/01/2010
1 7 07/01/2010
1 8 08/01/2010
1 9 09/01/2010
1 10 10/01/2010
1 11 11/01/2010 X
1 12 12/01/2010
1 13 13/01/2010
1 14 14/01/2010 X
2 1 15/01/2010
2 2 16/01/2010 Y
2 3 17/01/2010
2 4 18/01/2010
2 5 19/01/2010
2 6 20/01/2010
2 7 21/01/2010
2 8 22/01/2010
2 9 23/01/2010
2 10 24/01/2010
2 11 25/01/2010 X
2 12 26/01/2010
2 13 27/01/2010
2 14 28/01/2010 X
3 1 29/01/2010
3 2 30/01/2010 X
3 3 31/01/2010
3 4 01/02/2010
3 5 02/02/2010 X
3 6 03/02/2010
3 7 04/02/2010
3 8 05/02/2010
3 9 06/02/2010
3 10 07/02/2010
3 11 08/02/2010 X
3 12 09/02/2010
3 13 10/02/2010
3 14 11/02/2010 X

[ADezii]

This is definitely 'possible' - just let me work out the logic in my mind, and I'll come up with something for you. Stay tuned...

P.S. - 02/02/2010 should not be part of the Result Set, should it?

[josephsimonbenn]

Heres my thought if you could come up with a code to do this, i'd be most grateful:

As an example lets say mr smith has to get a bill every day 1 and day 15 of a 28 day cycle. For the next 10 cycles. Starting today... So....

[The Number Of Days in a Cycle (28)] * [Number Of Cycles(10)] = (280)Days from start to end.

Then using the DateAdd function:
[Start Date(10/04/2010)] + (280)Days from start to end = The End Date
(15/01/2011)

Now I have a start and end date... Now the code can populate a table with all the dates in between?????

If the code adds another field in the temporary table called 'CycleDayNumber ' And increments in value [The Number Of Days in a Cycle (28)] on a loop...

Then it should be able to somehow (possible another field) mark out all the day 1 and day 15s and delete the others????

[ADezii]

I do believe that I have a solution and will Post it later.

[josephsimonbenn]

Excellent :D thanks :D

[ADezii]

1. I arrived at a solution utilizing a Public Function which accepts 4 Arguments. This Function contains all the necessary Logic, and hopefully dumps the Results into a Table named tblResults.
2. Rather than go into a detailed explanation of how the code works, I'll simply Post the Function Definition below along with a sample Call to the Function, and the Results generated from that Call. I also made available to you the Test Database for this Thread as an Attachment so that you can easily see what is going on. I feel that the code is adequately commented, but should you have any questions on anything at all, please feel free to ask.
   Function Definition:
   

   Public Function fPopulateSchedule(bytNumOfCycles As Byte, bytDaysInCycle As Byte, dteDate As Date, strRndNums As String)
   On Error GoTo Err_fPopulateSchedule
   'bytNumOfCycles - The actual Number of Cycles/Times to repeat a Cycle
   'bytDaysInCycle - Days contained within a Cycle
   'dteDate        - Starting Date
   'strRndNums     - A Comma-Delimited String containing a 'SEQUENTIAL' listing of Random Days
   '                 within the Cycle. The list must be sequential or the code will crash. I'll
   '                 leave this up to you in order to limit the amount of code. The last number
   '                 in sequence must be <= the Days in the Cycle (bytDaysInCycle). Again, for
   '                 the sake of brevity, I'll leave this Validation Check to you.
   
   Dim varRandoms As Variant
   Dim bytCycleCounter As Byte
   Dim bytDaysInCycleCtr As Byte
   Dim bytOverallDayCtr As Byte
   Dim bytArrayCtr As Byte
   
   'Place the List of Random, Sequential Numbers into an Array
   varRandoms = Split(strRndNums, ",")
   
   'Initialize, not really necessary, but I like to for clarity
   bytOverallDayCtr = 0
   
   'If you do not wish to maintain existing Records in tblResults, then DELETE
   'ALL Previous Results (Records in tblResults) - Remove Comment from Line below (')
   'CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
   
   'Populate tblResults with the Cycle, Day in Cycle, and Random Days in Cycle
   For bytCycleCounter = 1 To bytNumOfCycles
     For bytDaysInCycleCtr = 1 To bytDaysInCycle
       For bytArrayCtr = LBound(varRandoms) To UBound(varRandoms)
         'Does the Day in the Cycle equal the Random Date?
         If bytDaysInCycleCtr = varRandoms(bytArrayCtr) Then
           CurrentDb.Execute "INSERT INTO tblResults ([Cycle], [Day], [Date]) VALUES (" & _
                              bytCycleCounter & ", " & bytDaysInCycleCtr & ", #" & _
                              DateAdd("d", bytOverallDayCtr, dteDate) & "#);", dbFailOnError
         End If
       Next
         bytOverallDayCtr = bytOverallDayCtr + 1
     Next
   Next
   
   'Let's see the results of all our hard work
   With DoCmd
     .OpenTable "tblResults", acViewNormal, acReadOnly
     .Maximize
   End With
   
   Exit_fPopulateSchedule:
     Exit Function
   
   Err_fPopulateSchedule:
     MsgBox Err.Description, vbExclamation, "Error in fPopulateSchedule()"
       Resume Exit_fPopulateSchedule
   End Function

3. Sample Function Call with your Parameters specified in Post#3:
   

   '3 Cycles, 14 Days per Cycle, Starting Date of 1/1/2010
   'Random Days in Cycle are 2, 11, and 14. Random Numbers
   'must be in sequence and cannot exceed 14 for this Example
   Call fPopulateSchedule(3, 14, #1/1/2010#, "2,11,14")

4. Results as they exist in tblResults for this Sample Call:
   

   Cycle	Day	 Date
   1	      2	 02/01/2010
   1	     11	 11/01/2010
   1	     14	 14/01/2010
   2	      2	 16/01/2010
   2	     11	 25/01/2010
   2	     14	 28/01/2010
   3	      2	 30/01/2010
   3	     11	 08/02/2010
   3	     14	 11/02/2010

5. Download the Attachment, it really says it all!

[josephsimonbenn]

Hey man this is absolutely awesome.... I only have one slight problem.... The code populates all records for all clients... I need an extra field in the results table to identify which customer its for... And i only want it to populate one customer when i hit the command button :\

Is this possible?!

How would I do it?!

[ADezii]

Are you saying that you wish to pass a Customer Name to the Function to populate tblResults with that Customer's Name also?

[josephsimonbenn]

Ok, all the dates are working except some are in American format, and others are in british format :S

For Example:

Mrs Smith's: fPopulateSchedu le(10, 21, 23/03/2010, 1)

Cycle Day Date
1 1 23/03/2010 Correct
2 1 13/04/2010 Correct
3 1 05/04/2010 Should be 04/05/2010!!!
4 1 25/05/2010 Correct
5 1 15/06/2010 Correct
6 1 07/06/2010 Should be 06/07/2010
7 1 27/07/2010 Correct
8 1 17/08/2010 Correct
9 1 09/07/2010 Should be 07/09/2010
10 1 28/09/2010 Correct

Same happens with Mr Davis: fPopulateSchedu le(6, 28, 17/03/2010, 1,8,15)

Cycle Day Date
1 1 17/03/2010 Correct
1 8 24/03/2010 Correct
1 15 31/03/2010 Correct
2 1 14/04/2010 Correct
2 8 21/04/2010 Correct
2 15 28/04/2010 Correct
3 1 05/12/2010 Should be 12/05/2010
3 8 19/05/2010 Correct
3 15 26/05/2010 Correct
4 1 06/09/2010 Should be 09/06/2010
4 8 16/06/2010 Correct
4 15 23/06/2010 Correct
5 1 07/07/2010 Correct
5 8 14/07/2010 Correct
5 15 21/07/2010 Correct
6 1 08/04/2010 Should be 04/08/2010
6 8 08/11/2010 Should be 11/08/2010
6 15 18/08/2010 Correct

Why would this be?!

[josephsimonbenn]

I only want it to populate the record i'm using not ALL records and I need it to copy the customer reference/name to the results table or else i'm seeing all results for all customers :\

Sorry bout this :)

[ADezii]

Download the New Attachment which will contain everything you need to continue with just minor modifications.

[josephsimonbenn]

ok lets see how we get on with this one :) xxx

[josephsimonbenn]

Hi Adeziii

Just wanted to say thanks - that all seems to be working amazinggggly :)

One slight hiccup is that i have one customer who is on a 90 day system over a 2 year contract. When i put the numbers in it came up with an overflow error!! But if thats the only glitch and It can't be overcome, i guess i'll live with working her dates out manually.