473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Setting Automatic Dates from a roll in Interval

Help! Help!Help!

Hi Guys,
I am stuck and need your help.

Quick Background.

I am working with Access 2010.
I have a table called "packages" which contain work streams that engineers have got to service periodically within that Month its due.

Each package has a set number of times in a year that a service must be carried out, Some are monthly, others are quarterly, Bi-annually and Annually.

My Table Fields.

I have created the Packages table with the fields;
-Package ID
-
Name of Package
-Service Interval (Monthly, Quarterly, Bi-annually or Annually)
-Last Service Due Date
-Current Service due Date
-Next Service Due Date

Now, here is my one million dollar question.

Supposing i have a starting point (First ever service date).

Now that i have the Interval, how can i set from a module, or expression or whatever, all the due dates to be generated automatically?


This thing has been straining my brain a lot, someone might just tweak it a little bit and come out with the solution which will make me feel so dump...

Thanks for your help

Regards
Jul 14 '16 #1

✓ answered by ADezii

  1. The following Code will generate all the Service Dates for the next 3 years (up to 12/31/2019) based on a Last Service Date Due which defaults to 7/1/2016 and 1 of 4 Interval Values. Simply Pass to the Function the Interval (Monthly, Quarterly, B-annually, or Annually) and a Base Date. Change the Value of the Constant conYEARS within the Function to set the Term.
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateServiceDueDates(strInterval As String, dteLastServiceDueDate As Date)
    2. Dim intIncrement As Integer
    3. Const conYEARS As Byte = 3      'Calculation Period
    4.  
    5. intIncrement = 1    'Initialize
    6.  
    7. Select Case strInterval
    8.   Case "Monthly"
    9.    Debug.Print "Monthly"
    10.     Do While DateAdd("m", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    11.       Debug.Print "  |-- " & DateAdd("m", intIncrement, dteLastServiceDueDate)
    12.         intIncrement = intIncrement + 1
    13.     Loop
    14.   Case "Quarterly"
    15.    Debug.Print "Quarterly"
    16.     Do While DateAdd("q", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    17.       Debug.Print "  |-- " & DateAdd("q", intIncrement, dteLastServiceDueDate)
    18.         intIncrement = intIncrement + 1
    19.     Loop
    20.   Case "Bi-annually"
    21.    Debug.Print "Annually"
    22.     Do While DateAdd("m", intIncrement * 6, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    23.       Debug.Print "  |-- " & DateAdd("m", intIncrement * 6, dteLastServiceDueDate)
    24.         intIncrement = intIncrement + 1
    25.     Loop
    26.   Case "Annually"
    27.    Debug.Print "Annually"
    28.     Do While DateAdd("yyyy", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    29.       Debug.Print "  |-- " & DateAdd("yyyy", intIncrement, dteLastServiceDueDate)
    30.         intIncrement = intIncrement + 1
    31.     Loop
    32.   Case Else     'do nothing
    33. End Select
    34. End Function
  3. Sample Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Monthly", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Quarterly", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Bi-annually", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Annually", #7/1/2016#)
  4. OUTPUTS (same sequence as Calls):
    Expand|Select|Wrap|Line Numbers
    1. Monthly
    2.   |-- 8/1/2016
    3.   |-- 9/1/2016
    4.   |-- 10/1/2016
    5.   |-- 11/1/2016
    6.   |-- 12/1/2016
    7.   |-- 1/1/2017
    8.   |-- 2/1/2017
    9.   |-- 3/1/2017
    10.   |-- 4/1/2017
    11.   |-- 5/1/2017
    12.   |-- 6/1/2017
    13.   |-- 7/1/2017
    14.   |-- 8/1/2017
    15.   |-- 9/1/2017
    16.   |-- 10/1/2017
    17.   |-- 11/1/2017
    18.   |-- 12/1/2017
    19.   |-- 1/1/2018
    20.   |-- 2/1/2018
    21.   |-- 3/1/2018
    22.   |-- 4/1/2018
    23.   |-- 5/1/2018
    24.   |-- 6/1/2018
    25.   |-- 7/1/2018
    26.   |-- 8/1/2018
    27.   |-- 9/1/2018
    28.   |-- 10/1/2018
    29.   |-- 11/1/2018
    30.   |-- 12/1/2018
    31.   |-- 1/1/2019
    32.   |-- 2/1/2019
    33.   |-- 3/1/2019
    34.   |-- 4/1/2019
    35.   |-- 5/1/2019
    36.   |-- 6/1/2019
    37.   |-- 7/1/2019
    38.   |-- 8/1/2019
    39.   |-- 9/1/2019
    40.   |-- 10/1/2019
    41.   |-- 11/1/2019
    42.   |-- 12/1/2019
    Expand|Select|Wrap|Line Numbers
    1. Quarterly
    2.   |-- 10/1/2016
    3.   |-- 1/1/2017
    4.   |-- 4/1/2017
    5.   |-- 7/1/2017
    6.   |-- 10/1/2017
    7.   |-- 1/1/2018
    8.   |-- 4/1/2018
    9.   |-- 7/1/2018
    10.   |-- 10/1/2018
    11.   |-- 1/1/2019
    12.   |-- 4/1/2019
    13.   |-- 7/1/2019
    14.   |-- 10/1/2019
    15.  
    Expand|Select|Wrap|Line Numbers
    1. Bi-Annually
    2.   |-- 1/1/2017
    3.   |-- 7/1/2017
    4.   |-- 1/1/2018
    5.   |-- 7/1/2018
    6.   |-- 1/1/2019
    7.   |-- 7/1/2019
    Expand|Select|Wrap|Line Numbers
    1. Annually
    2.   |-- 7/1/2017
    3.   |-- 7/1/2018
    4.   |-- 7/1/2019

10 1107
PhilOfWalton
1,430 Expert 1GB
Have a look at the DateAdd Function (Read more)

Phil
Jul 14 '16 #2
ADezii
8,834 Expert 8TB
  1. The following Code will generate all the Service Dates for the next 3 years (up to 12/31/2019) based on a Last Service Date Due which defaults to 7/1/2016 and 1 of 4 Interval Values. Simply Pass to the Function the Interval (Monthly, Quarterly, B-annually, or Annually) and a Base Date. Change the Value of the Constant conYEARS within the Function to set the Term.
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateServiceDueDates(strInterval As String, dteLastServiceDueDate As Date)
    2. Dim intIncrement As Integer
    3. Const conYEARS As Byte = 3      'Calculation Period
    4.  
    5. intIncrement = 1    'Initialize
    6.  
    7. Select Case strInterval
    8.   Case "Monthly"
    9.    Debug.Print "Monthly"
    10.     Do While DateAdd("m", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    11.       Debug.Print "  |-- " & DateAdd("m", intIncrement, dteLastServiceDueDate)
    12.         intIncrement = intIncrement + 1
    13.     Loop
    14.   Case "Quarterly"
    15.    Debug.Print "Quarterly"
    16.     Do While DateAdd("q", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    17.       Debug.Print "  |-- " & DateAdd("q", intIncrement, dteLastServiceDueDate)
    18.         intIncrement = intIncrement + 1
    19.     Loop
    20.   Case "Bi-annually"
    21.    Debug.Print "Annually"
    22.     Do While DateAdd("m", intIncrement * 6, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    23.       Debug.Print "  |-- " & DateAdd("m", intIncrement * 6, dteLastServiceDueDate)
    24.         intIncrement = intIncrement + 1
    25.     Loop
    26.   Case "Annually"
    27.    Debug.Print "Annually"
    28.     Do While DateAdd("yyyy", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
    29.       Debug.Print "  |-- " & DateAdd("yyyy", intIncrement, dteLastServiceDueDate)
    30.         intIncrement = intIncrement + 1
    31.     Loop
    32.   Case Else     'do nothing
    33. End Select
    34. End Function
  3. Sample Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Monthly", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Quarterly", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Bi-annually", #7/1/2016#)
    Expand|Select|Wrap|Line Numbers
    1. Call fGenerateServiceDueDates("Annually", #7/1/2016#)
  4. OUTPUTS (same sequence as Calls):
    Expand|Select|Wrap|Line Numbers
    1. Monthly
    2.   |-- 8/1/2016
    3.   |-- 9/1/2016
    4.   |-- 10/1/2016
    5.   |-- 11/1/2016
    6.   |-- 12/1/2016
    7.   |-- 1/1/2017
    8.   |-- 2/1/2017
    9.   |-- 3/1/2017
    10.   |-- 4/1/2017
    11.   |-- 5/1/2017
    12.   |-- 6/1/2017
    13.   |-- 7/1/2017
    14.   |-- 8/1/2017
    15.   |-- 9/1/2017
    16.   |-- 10/1/2017
    17.   |-- 11/1/2017
    18.   |-- 12/1/2017
    19.   |-- 1/1/2018
    20.   |-- 2/1/2018
    21.   |-- 3/1/2018
    22.   |-- 4/1/2018
    23.   |-- 5/1/2018
    24.   |-- 6/1/2018
    25.   |-- 7/1/2018
    26.   |-- 8/1/2018
    27.   |-- 9/1/2018
    28.   |-- 10/1/2018
    29.   |-- 11/1/2018
    30.   |-- 12/1/2018
    31.   |-- 1/1/2019
    32.   |-- 2/1/2019
    33.   |-- 3/1/2019
    34.   |-- 4/1/2019
    35.   |-- 5/1/2019
    36.   |-- 6/1/2019
    37.   |-- 7/1/2019
    38.   |-- 8/1/2019
    39.   |-- 9/1/2019
    40.   |-- 10/1/2019
    41.   |-- 11/1/2019
    42.   |-- 12/1/2019
    Expand|Select|Wrap|Line Numbers
    1. Quarterly
    2.   |-- 10/1/2016
    3.   |-- 1/1/2017
    4.   |-- 4/1/2017
    5.   |-- 7/1/2017
    6.   |-- 10/1/2017
    7.   |-- 1/1/2018
    8.   |-- 4/1/2018
    9.   |-- 7/1/2018
    10.   |-- 10/1/2018
    11.   |-- 1/1/2019
    12.   |-- 4/1/2019
    13.   |-- 7/1/2019
    14.   |-- 10/1/2019
    15.  
    Expand|Select|Wrap|Line Numbers
    1. Bi-Annually
    2.   |-- 1/1/2017
    3.   |-- 7/1/2017
    4.   |-- 1/1/2018
    5.   |-- 7/1/2018
    6.   |-- 1/1/2019
    7.   |-- 7/1/2019
    Expand|Select|Wrap|Line Numbers
    1. Annually
    2.   |-- 7/1/2017
    3.   |-- 7/1/2018
    4.   |-- 7/1/2019
Jul 14 '16 #3
Hi ADezii,

Thanks so much for you elaborate reply, i am going to sit down and go through the codes and test it tonight.

One question though from perusing though the code, Will this be able to display the right active dates on the fields and be able to hide the rest as should be?
for instance, in September, the Current Service Due date will be 1/09/2016. the Last Service date will be 1/08/2016 and next will be 1/10/2016. In October, those fields will rearrange accordingly?

One last question i forgot to include earlier,

How can i keep the historical records of all the past service dates, for instance, if its October now, i may need to check whether the previous 3 services were carried. Basically, i wish to tell Access to store every service date as a historical record because i will use this table in a query with another Table with more fields.

I am sorry i am asking to much, kindly help.

Regards
Jul 14 '16 #4
ADezii
8,834 Expert 8TB
  1. What you are asking is becoming increasingly more complex. Let's suppose you have a Service Interval of 1 month for an Item and the following Dates:
    Expand|Select|Wrap|Line Numbers
    1. Last Service Date: 7/1/2016
    2. Current Service Due Date: 8/1/2016
    3. Next Service Due Date: 9/1/2016
  2. When 8/1/2016 rolls around and the Service for this Item has been completed (you need to monitor for this), you need the following Fields Updated:
    Expand|Select|Wrap|Line Numbers
    1. Last Service Date:==> 8/1/2016
    2. Current Service Due Date:==> 9/1/2016
    3. Next Service Due Date:==> 10/1/2016
    4.  
  3. You also need to maintain Archives of all the previous Service Dates?
  4. It appears to me that this would be no small task.
Jul 14 '16 #5
Thanks ADezii,

Thanks for your quick reply, yes, that's what i was hoping to achieve.
I went through your code and really impressed, its given me a very good start.

Thank you so much.
Jul 15 '16 #6
ADezii
8,834 Expert 8TB
You are quite welcome, when I have some additional time I'll see what else I can come up with.
Jul 15 '16 #7
I really Appreciate, you have been great.
I await any further help you can come up with
Jul 15 '16 #8
ADezii
8,834 Expert 8TB
I have put together for you, what I feel, is the simplest Demo that will illustrate all the points that you requested. It is by no means comprehensive but is only meant to be a good starting point for future development. Download the Attachment and look at all aspects of the Database, specifically:
  1. The three Tables and the Relationships between them.
  2. The Fields in these three Tables, their Data Types, and their Properties.
  3. The Lookup for the [Service_Interval] Field in tblPackages.
  4. The Record Source for the Main Form.
  5. The Code in the Current() Event of the Main Form.
  6. The Code in the Click() Event of the Update and Archive Command Button on the Main Form.
  7. Code in the Click() Event of the View Archives Command Button.
  8. Once you have mastered all this, check back in and let us know how you are making out.
Attached Files
File Type: zip Demo.zip (30.3 KB, 42 views)
Jul 15 '16 #9
Wow,
The Demo you have highlighted here seems to answer all the questions i have.
All i need to do is factor the codes into my tables.
This is great.
Very helpful contributor you are ADezii
Jul 18 '16 #10
ADezii
8,834 Expert 8TB
Good Luck with your Project - we are here should you need us.
Jul 18 '16 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Lynn | last post by:
On a form I have Date_Start Date_End I have a new Date_Start1 Date_End1 which the use inputs. I need to validate that Date_Start1 and...
3
by: Steven Scaife | last post by:
I am trying to work out the length of time that a page was opened till it reached another page, I amusing the script below End_Of_Call = formatdatetime(now(), 0) 'work out the duration of the...
4
by: Jorey Bump | last post by:
I can retrieve today's date: mysql> SELECT CURDATE() AS begin; +------------+ | begin | +------------+ | 2005-06-01 | +------------+ 1 row in set (0.00 sec)
1
by: bigfella | last post by:
Hi Everyone, I am using the following code (many thanks for the code folks BTW) to generate 3 schedules in three tables one is a schedule with 7 day intervals and two are daily schedules. The...
3
by: Lumpierbritches | last post by:
Can this be done in access by a lay person? Also, are there any tutorials for this type of addition to your applications? I'm trying to use a calendar control to set a date in the future for a to...
2
by: Rachel Suddeth | last post by:
Is there a way to have the non-selectable dates (those before MinDate and after MaxDate) draw differently so my users can see right away what dates aren't allowed? I'm not seeing it... ...
4
by: Rickey Tom | last post by:
This has to be a very common question, but my search did not come up with an answer. I needed to set an expiration time for a cookie. In .NET, is seems that the server-side code is used to set...
2
by: Ty Moffett | last post by:
Below is a set of insructions that changes the registry keys pertaining to Automatic Updates on my Windows XP box. I can make changes using the control panel applet and see the changes reflected...
2
by: pruebauno | last post by:
I am currently working on a tricky problem at work. I googled around a bit, but "time intervals" did not come up with anything useful. Although I have some rough idea of how I could solve it, I...
6
by: newsteve1 | last post by:
hi, this should be simple but its stumping me, I am trying to make a slideshow that pulls random images at a random time interval (between 1 and 4 seconds). The images part works fine, and I...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.