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
- 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.
- Function Definition:
- Public Function fGenerateServiceDueDates(strInterval As String, dteLastServiceDueDate As Date)
-
Dim intIncrement As Integer
-
Const conYEARS As Byte = 3 'Calculation Period
-
-
intIncrement = 1 'Initialize
-
-
Select Case strInterval
-
Case "Monthly"
-
Debug.Print "Monthly"
-
Do While DateAdd("m", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("m", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Quarterly"
-
Debug.Print "Quarterly"
-
Do While DateAdd("q", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("q", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Bi-annually"
-
Debug.Print "Annually"
-
Do While DateAdd("m", intIncrement * 6, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("m", intIncrement * 6, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Annually"
-
Debug.Print "Annually"
-
Do While DateAdd("yyyy", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("yyyy", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case Else 'do nothing
-
End Select
-
End Function
- Sample Function Calls:
- Call fGenerateServiceDueDates("Monthly", #7/1/2016#)
- Call fGenerateServiceDueDates("Quarterly", #7/1/2016#)
- Call fGenerateServiceDueDates("Bi-annually", #7/1/2016#)
- Call fGenerateServiceDueDates("Annually", #7/1/2016#)
- OUTPUTS (same sequence as Calls):
- Monthly
-
|-- 8/1/2016
-
|-- 9/1/2016
-
|-- 10/1/2016
-
|-- 11/1/2016
-
|-- 12/1/2016
-
|-- 1/1/2017
-
|-- 2/1/2017
-
|-- 3/1/2017
-
|-- 4/1/2017
-
|-- 5/1/2017
-
|-- 6/1/2017
-
|-- 7/1/2017
-
|-- 8/1/2017
-
|-- 9/1/2017
-
|-- 10/1/2017
-
|-- 11/1/2017
-
|-- 12/1/2017
-
|-- 1/1/2018
-
|-- 2/1/2018
-
|-- 3/1/2018
-
|-- 4/1/2018
-
|-- 5/1/2018
-
|-- 6/1/2018
-
|-- 7/1/2018
-
|-- 8/1/2018
-
|-- 9/1/2018
-
|-- 10/1/2018
-
|-- 11/1/2018
-
|-- 12/1/2018
-
|-- 1/1/2019
-
|-- 2/1/2019
-
|-- 3/1/2019
-
|-- 4/1/2019
-
|-- 5/1/2019
-
|-- 6/1/2019
-
|-- 7/1/2019
-
|-- 8/1/2019
-
|-- 9/1/2019
-
|-- 10/1/2019
-
|-- 11/1/2019
-
|-- 12/1/2019
- Quarterly
-
|-- 10/1/2016
-
|-- 1/1/2017
-
|-- 4/1/2017
-
|-- 7/1/2017
-
|-- 10/1/2017
-
|-- 1/1/2018
-
|-- 4/1/2018
-
|-- 7/1/2018
-
|-- 10/1/2018
-
|-- 1/1/2019
-
|-- 4/1/2019
-
|-- 7/1/2019
-
|-- 10/1/2019
-
- Bi-Annually
-
|-- 1/1/2017
-
|-- 7/1/2017
-
|-- 1/1/2018
-
|-- 7/1/2018
-
|-- 1/1/2019
-
|-- 7/1/2019
- Annually
-
|-- 7/1/2017
-
|-- 7/1/2018
-
|-- 7/1/2019
10 1107 - 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.
- Function Definition:
- Public Function fGenerateServiceDueDates(strInterval As String, dteLastServiceDueDate As Date)
-
Dim intIncrement As Integer
-
Const conYEARS As Byte = 3 'Calculation Period
-
-
intIncrement = 1 'Initialize
-
-
Select Case strInterval
-
Case "Monthly"
-
Debug.Print "Monthly"
-
Do While DateAdd("m", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("m", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Quarterly"
-
Debug.Print "Quarterly"
-
Do While DateAdd("q", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("q", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Bi-annually"
-
Debug.Print "Annually"
-
Do While DateAdd("m", intIncrement * 6, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("m", intIncrement * 6, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case "Annually"
-
Debug.Print "Annually"
-
Do While DateAdd("yyyy", intIncrement, dteLastServiceDueDate) < DateSerial(Year(dteLastServiceDueDate) + conYEARS, 12, 31)
-
Debug.Print " |-- " & DateAdd("yyyy", intIncrement, dteLastServiceDueDate)
-
intIncrement = intIncrement + 1
-
Loop
-
Case Else 'do nothing
-
End Select
-
End Function
- Sample Function Calls:
- Call fGenerateServiceDueDates("Monthly", #7/1/2016#)
- Call fGenerateServiceDueDates("Quarterly", #7/1/2016#)
- Call fGenerateServiceDueDates("Bi-annually", #7/1/2016#)
- Call fGenerateServiceDueDates("Annually", #7/1/2016#)
- OUTPUTS (same sequence as Calls):
- Monthly
-
|-- 8/1/2016
-
|-- 9/1/2016
-
|-- 10/1/2016
-
|-- 11/1/2016
-
|-- 12/1/2016
-
|-- 1/1/2017
-
|-- 2/1/2017
-
|-- 3/1/2017
-
|-- 4/1/2017
-
|-- 5/1/2017
-
|-- 6/1/2017
-
|-- 7/1/2017
-
|-- 8/1/2017
-
|-- 9/1/2017
-
|-- 10/1/2017
-
|-- 11/1/2017
-
|-- 12/1/2017
-
|-- 1/1/2018
-
|-- 2/1/2018
-
|-- 3/1/2018
-
|-- 4/1/2018
-
|-- 5/1/2018
-
|-- 6/1/2018
-
|-- 7/1/2018
-
|-- 8/1/2018
-
|-- 9/1/2018
-
|-- 10/1/2018
-
|-- 11/1/2018
-
|-- 12/1/2018
-
|-- 1/1/2019
-
|-- 2/1/2019
-
|-- 3/1/2019
-
|-- 4/1/2019
-
|-- 5/1/2019
-
|-- 6/1/2019
-
|-- 7/1/2019
-
|-- 8/1/2019
-
|-- 9/1/2019
-
|-- 10/1/2019
-
|-- 11/1/2019
-
|-- 12/1/2019
- Quarterly
-
|-- 10/1/2016
-
|-- 1/1/2017
-
|-- 4/1/2017
-
|-- 7/1/2017
-
|-- 10/1/2017
-
|-- 1/1/2018
-
|-- 4/1/2018
-
|-- 7/1/2018
-
|-- 10/1/2018
-
|-- 1/1/2019
-
|-- 4/1/2019
-
|-- 7/1/2019
-
|-- 10/1/2019
-
- Bi-Annually
-
|-- 1/1/2017
-
|-- 7/1/2017
-
|-- 1/1/2018
-
|-- 7/1/2018
-
|-- 1/1/2019
-
|-- 7/1/2019
- Annually
-
|-- 7/1/2017
-
|-- 7/1/2018
-
|-- 7/1/2019
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
- 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:
- Last Service Date: 7/1/2016
-
Current Service Due Date: 8/1/2016
-
Next Service Due Date: 9/1/2016
- 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:
- Last Service Date:==> 8/1/2016
-
Current Service Due Date:==> 9/1/2016
-
Next Service Due Date:==> 10/1/2016
-
- You also need to maintain Archives of all the previous Service Dates?
- It appears to me that this would be no small task.
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.
You are quite welcome, when I have some additional time I'll see what else I can come up with.
I really Appreciate, you have been great.
I await any further help you can come up with
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: - The three Tables and the Relationships between them.
- The Fields in these three Tables, their Data Types, and their Properties.
- The Lookup for the [Service_Interval] Field in tblPackages.
- The Record Source for the Main Form.
- The Code in the Current() Event of the Main Form.
- The Code in the Click() Event of the Update and Archive Command Button on the Main Form.
- Code in the Click() Event of the View Archives Command Button.
- Once you have mastered all this, check back in and let us know how you are making out.
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
Good Luck with your Project - we are here should you need us.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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)
|
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...
|
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...
|
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...
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
|
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...
| |