459,341 Members | 1,726 Online
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

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

P: 32
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.
Attached Files
 MyDb.zip (255.5 KB, 84 views)
Apr 9 '10 #1

Not exactly sure what you mean by the above Statement, but if you are looking for
Expand|Select|Wrap|Line Numbers
1. <[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:
Expand|Select|Wrap|Line Numbers
which would evaluate to:
Expand|Select|Wrap|Line Numbers
1. 5/26/2010
Is this the logic that you are looking for?

38 Replies

 Expert 5K+ P: 8,699 Not exactly sure what you mean by the above Statement, but if you are looking for Expand|Select|Wrap|Line Numbers <[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: Expand|Select|Wrap|Line Numbers DateAdd("d",(28 * 4),#2/3/2010#) which would evaluate to: Expand|Select|Wrap|Line Numbers 5/26/2010 Is this the logic that you are looking for? Apr 10 '10 #2

 P: 32 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 Apr 10 '10 #3

 Expert 5K+ P: 8,699 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? Apr 10 '10 #4

 P: 32 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???? Apr 10 '10 #5

 Expert 5K+ P: 8,699 I do believe that I have a solution and will Post it later. Apr 10 '10 #6

 P: 32 Excellent :D thanks :D Apr 10 '10 #7

Expert 5K+
P: 8,699
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:
Expand|Select|Wrap|Line Numbers
1. Public Function fPopulateSchedule(bytNumOfCycles As Byte, bytDaysInCycle As Byte, dteDate As Date, strRndNums As String)
2. On Error GoTo Err_fPopulateSchedule
3. 'bytNumOfCycles - The actual Number of Cycles/Times to repeat a Cycle
4. 'bytDaysInCycle - Days contained within a Cycle
5. 'dteDate        - Starting Date
6. 'strRndNums     - A Comma-Delimited String containing a 'SEQUENTIAL' listing of Random Days
7. '                 within the Cycle. The list must be sequential or the code will crash. I'll
8. '                 leave this up to you in order to limit the amount of code. The last number
9. '                 in sequence must be <= the Days in the Cycle (bytDaysInCycle). Again, for
10. '                 the sake of brevity, I'll leave this Validation Check to you.
11.
12. Dim varRandoms As Variant
13. Dim bytCycleCounter As Byte
14. Dim bytDaysInCycleCtr As Byte
15. Dim bytOverallDayCtr As Byte
16. Dim bytArrayCtr As Byte
17.
18. 'Place the List of Random, Sequential Numbers into an Array
19. varRandoms = Split(strRndNums, ",")
20.
21. 'Initialize, not really necessary, but I like to for clarity
22. bytOverallDayCtr = 0
23.
24. 'If you do not wish to maintain existing Records in tblResults, then DELETE
25. 'ALL Previous Results (Records in tblResults) - Remove Comment from Line below (')
26. 'CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
27.
28. 'Populate tblResults with the Cycle, Day in Cycle, and Random Days in Cycle
29. For bytCycleCounter = 1 To bytNumOfCycles
30.   For bytDaysInCycleCtr = 1 To bytDaysInCycle
31.     For bytArrayCtr = LBound(varRandoms) To UBound(varRandoms)
32.       'Does the Day in the Cycle equal the Random Date?
33.       If bytDaysInCycleCtr = varRandoms(bytArrayCtr) Then
34.         CurrentDb.Execute "INSERT INTO tblResults ([Cycle], [Day], [Date]) VALUES (" & _
35.                            bytCycleCounter & ", " & bytDaysInCycleCtr & ", #" & _
36.                            DateAdd("d", bytOverallDayCtr, dteDate) & "#);", dbFailOnError
37.       End If
38.     Next
39.       bytOverallDayCtr = bytOverallDayCtr + 1
40.   Next
41. Next
42.
43. 'Let's see the results of all our hard work
44. With DoCmd
46.   .Maximize
47. End With
48.
49. Exit_fPopulateSchedule:
50.   Exit Function
51.
52. Err_fPopulateSchedule:
53.   MsgBox Err.Description, vbExclamation, "Error in fPopulateSchedule()"
54.     Resume Exit_fPopulateSchedule
55. End Function
3. Sample Function Call with your Parameters specified in Post#3:
Expand|Select|Wrap|Line Numbers
1. '3 Cycles, 14 Days per Cycle, Starting Date of 1/1/2010
2. 'Random Days in Cycle are 2, 11, and 14. Random Numbers
3. 'must be in sequence and cannot exceed 14 for this Example
4. Call fPopulateSchedule(3, 14, #1/1/2010#, "2,11,14")
4. Results as they exist in tblResults for this Sample Call:
Expand|Select|Wrap|Line Numbers
1. Cycle    Day     Date
2. 1          2     02/01/2010
3. 1         11     11/01/2010
4. 1         14     14/01/2010
5. 2          2     16/01/2010
6. 2         11     25/01/2010
7. 2         14     28/01/2010
8. 3          2     30/01/2010
9. 3         11     08/02/2010
10. 3         14     11/02/2010
Attached Files
 Populate a Schedule.zip (20.9 KB, 80 views)
Apr 10 '10 #8

 P: 32 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?! Apr 10 '10 #9

 Expert 5K+ P: 8,699 Are you saying that you wish to pass a Customer Name to the Function to populate tblResults with that Customer's Name also? Apr 10 '10 #10

 P: 32 Ok, all the dates are working except some are in American format, and others are in british format :S For Example: Mrs Smith's: fPopulateSchedule(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: fPopulateSchedule(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?! Apr 10 '10 #11

 P: 32 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 :) Apr 10 '10 #12

Expert 5K+
P: 8,699
Download the New Attachment which will contain everything you need to continue with just minor modifications.
Attached Files
 Populate a Schedule_2.zip (78.9 KB, 90 views)
Apr 11 '10 #13

 P: 32 ok lets see how we get on with this one :) xxx Apr 11 '10 #14

 P: 32 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. Apr 11 '10 #15

 P: 32 Hey man, For the SQL part where it inserts the results into a new table.... How would i add more fields from my original Table? I need to add a [PackageID] and a [quickname] So basically the fields in need from my original source into the results table are: Cycle Day Date Customer PackageID QuickName Apr 11 '10 #16

 Expert 5K+ P: 8,699 I'll take care of these minor details for you in a day or two, since we have gone this far. As far as the Date Formats, how would you know when to display American as opposed to British, or vice versa? P.S. - I also need the set of values that are causing the Overflow Error to occur (Cycles, Days Per Cycle, Start Date, Random Days). Apr 11 '10 #17

 P: 32 When i woke up this morning and tried the function, it has turned the numbers around again!!!!?!?!!? Apr 11 '10 #18

 P: 32 Re Date formats ~ I always want them in the format dd/mm/yyyy My maximum cycle is 90 days over 5 years if that helps with the definitions :) I'm baffled as to how comes it was working last night but its turning the dates around again today!!!! Apr 11 '10 #19

Expert 5K+
P: 8,699
Here is the latest Version with some changes made including the inclusion of the 2 Fields, changes to the Function Code and SQL Statement, Field Type changes, etc. Not sure how you are retrieving Values for these 2 New Fields, so I just hard-coded them into the Function Call. See the Attachment, and Good Luck. BTW, how are you retrieving your Random Numbers?
Attached Files
 Populate a Schedule_3.zip (78.4 KB, 62 views)
Apr 11 '10 #20

 P: 32 haha they're decided by someone else!!! Basically, my boss says to me: "Mr Smith - is on a 3 weekly, day 1 and day 8 only... He starts tomorrow - whens his last date? And what are all the dates in between?!" and i have to come up with the answer - i usually have to spend hours with a pencil and a calendar lol. So i've decided to try this out!!!! I'll let u know how i get on with it :D Apr 11 '10 #21

 P: 32 nope... some dates are still the wrong way round.... And Now I'm gettin an error - "An expression you entered is the wrong data type for one of the arguments" :S Apr 11 '10 #22

 Expert 5K+ P: 8,699 Is [PackageID] a Numeric Field and is [QuickName] Textual? Apr 11 '10 #23

 P: 32 PackageID > Long Integer (Autonumber) Customer > Long Integer QuickName> Text ProposedStartDate> Short Date #dd/mm/yyyy# DaysPerCycle> Long Integer CyclesPerCourse> Long Integer - (These two fields produce a field CPD> Lookup List > Cycles/Months/Years - Called literal Cycles on the form - Which is what I use as your - bytNumOfCycles) DaysInCycle> Text Apr 11 '10 #24

 Expert 5K+ P: 8,699 Why is DaysInCycle Text? Apr 11 '10 #25

 P: 32 Cause thats ur string of 1,5,7,9 etc!!! Ur code removes the commas?! Apr 11 '10 #26

 Expert 5K+ P: 8,699 We can go back and forth forever like this. How about Uploading the most current Database that you are working on, and I'll have a look tomorrow. Apr 11 '10 #27

Expert 5K+
P: 8,699
Here are the latest Revisions:
Attached Files
 Populate a Schedule_4.zip (85.7 KB, 71 views)
Apr 12 '10 #28

P: 32
Hope you have a chance to have a little look mate

thanks much appreciated
Attached Files
Apr 12 '10 #29

 Expert 5K+ P: 8,699 I'm not using Access 2007, can you convert to Access 2003 and resend? Apr 12 '10 #30

 P: 32 sure but some functions may not work... i'll do it now :) Apr 12 '10 #31

P: 32
Here goes!! x
Attached Files
Apr 12 '10 #32

Expert 5K+
P: 8,699
You had a couple of problems primarily with the List Box and the Requerying of it. There also appeared to be errant code in some of the Modules, but you should be OK now. Good Luck.
Attached Files
 Populate a Schedule_5.zip (69.0 KB, 85 views)
Apr 12 '10 #33

 P: 32 Its all great except some dates still come the wrong way round on some records!!! ahha. Everything else is awesome, though dude!! Serious thanks!!! I duno what i can do about the date thing! x Apr 12 '10 #34

Expert 5K+
P: 8,699
1. Remove any Formatting from the [Date] Field in tblResults.
2. Change Code Line #10 in fPopulateSchedule() as indicated below:
Expand|Select|Wrap|Line Numbers
1. '...Code intentionally omitted
2. 'Populate tblResults with the Cycle, Day in Cycle, and Random Days in Cycle
3. For intCycleCounter = 1 To bytNumOfCycles
4.   For intDaysInCycleCtr = 1 To bytDaysInCycle
5.     For intArrayCtr = LBound(varRandoms) To UBound(varRandoms)
6.       'Does the Day in the Cycle equal the Random Date?
7.       If intDaysInCycleCtr = varRandoms(intArrayCtr) Then
8.         CurrentDb.Execute "INSERT INTO tblResults ([Cycle], [Day], [Date], [Patient], [ChmoPackageID], [Regimen]) VALUES (" & _
9.                            intCycleCounter & ", " & intDaysInCycleCtr & ", #" & _
10.                            Format(DateAdd("d", intOverallDayCtr, dteDate), "mm/dd/yyyy") & "#, '" & strCustomer & _
11.                            "', " & lngPackageID & ", '" & strQuickName & "');", dbFailOnError
12.       End If
13.     Next
14.       Debug.Print Format(DateAdd("d", intOverallDayCtr, dteDate), "dd/mm/yyyy")
15.       intOverallDayCtr = intOverallDayCtr + 1
16.   Next
17. Next
18. '...Code intentionally omitted
3. All Results will be correctly displayed but in the dd/mm/yyyy Format as opposed to mm/dd/yyyy.
4. A query can always Re-Format the [Date] Field if you so desire, and also be the RowSource for the list Box, as in:
Expand|Select|Wrap|Line Numbers
1. SELECT tblResults.Cycle, tblResults.Day, Format(tblResults.Date, "dd/mm/yyyy") As FDate,
2.  tblResults.Patient, tblResults.ChmoPackageID, tblResults.Regimen FROM tblResults;
5. See Attachment below for Date Problem Correction:
Attached Files
 Populate a Schedule_6.zip (70.1 KB, 60 views)
Apr 12 '10 #35

 P: 32 Wow - amazingggg - all works great!! Thank you so much you're a genius!! Stay tuned... more difficult questions on the way!!! Apr 13 '10 #36

 Expert 5K+ P: 8,699 @josephsimonbenn Glad to help you - how about giving me a break before any more difficult questions? (LOL). This Thread was really a challenge, but we pulled through. Apr 13 '10 #37

 P: 32 lol u can have a break.... i realli appreciate it!!! Only one thing i wish i'd have asked for is that that another field is added and it increments like so.... for example - Cycle Day [New Field] 1 1 A 1 8 B 1 15 C 2 1 A 2 8 B 2 15 C Haha when you fancy a challenge next!! Apr 13 '10 #38

Expert 5K+
P: 8,699
@josephsimonbenn
Happened to be very slow at work, and this was a relatively simple request. Here goes:

P.S. - I now consider this Thread closed.
Attached Files
 Populate a Schedule_7.zip (70.8 KB, 78 views)
Apr 13 '10 #39