Solved

Date progression over many years

Posted on 2014-09-23
141 Views
Experts, I hope I can explain this well.  How would you suggest to extend a date, for example, the end of each quarter for the next 15 years?  I don't know if a function would be how to do this or if it's rather simple.

Let's say the end of the next qtr is 9/31/2014.  Show the next end Of qtr dates  to a date 15 years into the future ([FacilityEndDate].  I need to display each of these dates.  I also need to apply this same logic to dates that are at the end of a month, semiannually and yearly as well.

Thank you
0
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 8
• 2
• +1

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 167 total points
ID: 40340755
test this code

Sub getLastQuarterDate()
Dim j As Integer, endQtrDate As Date, xDate As Date, startDate As Date
startDate = #9/30/2014#
endQtrDate = #12/31/2030#
j = 4
xDate = startDate
Do
Debug.Print xDate
xDate = DateSerial(Year(xDate), Month(xDate) + j, 0)
If xDate > endQtrDate Then Exit Do
Loop
End Sub
0

Author Comment

ID: 40340773
I will test this in the am.  Thank you for the response.
0

LVL 50

Assisted Solution

Gustav Brock earned 167 total points
ID: 40341023
I guess you wish to list these dates for the users. Then using callback functions is the optimum choice.
Look up the on-line help on these.

If you simply need comboboxes, study the attached demo (right-click, choose Save As, edit filename to UltimoDates.accdb, then save). It contains the callback functions listed here:
``````Public Function ListUltimoQuarters( _
ctl As Control, _
lngId As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) _
As Variant

' Count of months in a quarter.
Const cintQuarterMonthCount   As Integer = 3
' Count of quarters in a year.
Const cintYearQuarterCount    As Integer = 4
' Period for listing dates.
Const cintYears               As Integer = 15

' 2014-09-24. Cactus Data ApS, CPH.

Static datFirstDate   As Date
Static strFormat      As String
Static intRows        As Integer

Dim datDate           As Date
Dim lngOffset         As Long
Dim varValue          As Variant

Select Case intCode
Case acLBInitialize
datDate = Date
lngOffset = DatePart("q", datDate) * cintQuarterMonthCount
datFirstDate = DateSerial(Year(datDate), lngOffset + 1, 0)
intRows = cintYearQuarterCount * cintYears
strFormat = ctl.Format
varValue = True               ' True to initialize.
Case acLBOpen
varValue = Timer              ' Autogenerated unique ID.
Case acLBGetRowCount            ' Get rows.
varValue = intRows            ' Set number of rows.
Case acLBGetColumnCount         ' Get columns.
varValue = 1                  ' Set number of columns.
Case acLBGetColumnWidth         ' Get column width.
varValue = -1                 ' Use default width.
Case acLBGetValue               ' Get the data for each row.
Case acLBGetFormat              ' Format the data.
varValue = strFormat          ' Use format of control.
Case acLBEnd
' Do something when form with listbox closes or
' listbox is requeried.
End Select

' Return Value.
ListUltimoQuarters = varValue

End Function

Public Function ListUltimoMonths( _
ctl As Control, _
lngId As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) _
As Variant

' Count of months in a year.
Const cintYearMonthCount      As Integer = 12
' Period for listing dates.
Const cintYears               As Integer = 15

' 2014-09-24. Cactus Data ApS, CPH.

Static datFirstDate   As Date
Static strFormat      As String
Static intRows        As Integer

Dim datDate           As Date
Dim varValue          As Variant

Select Case intCode
Case acLBInitialize
datDate = Date
datFirstDate = DateSerial(Year(datDate), Month(datDate) + 1, 0)
intRows = cintYearMonthCount * cintYears
strFormat = ctl.Format
varValue = True               ' True to initialize.
Case acLBOpen
varValue = Timer              ' Autogenerated unique ID.
Case acLBGetRowCount            ' Get rows.
varValue = intRows            ' Set number of rows.
Case acLBGetColumnCount         ' Get columns.
varValue = 1                  ' Set number of columns.
Case acLBGetColumnWidth         ' Get column width.
varValue = -1                 ' Use default width.
Case acLBGetValue               ' Get the data for each row.
Case acLBGetFormat              ' Format the data.
varValue = strFormat          ' Use format of control.
Case acLBEnd
' Do something when form with listbox closes or
' listbox is requeried.
End Select

' Return Value.
ListUltimoMonths = varValue

End Function

Public Function ListUltimoSemiyears( _
ctl As Control, _
lngId As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) _
As Variant

' Count of months in a semiyear.
Const cintSemiyearMonthCount  As Integer = 6
' Count of semiyears in a year.
Const cintYearSemiyearCount   As Integer = 2
' Period for listing dates.
Const cintYears               As Integer = 15

' 2014-09-24. Cactus Data ApS, CPH.

Static datFirstDate   As Date
Static strFormat      As String
Static intRows        As Integer

Dim datDate           As Date
Dim lngOffset         As Long
Dim varValue          As Variant

Select Case intCode
Case acLBInitialize
datDate = Date
lngOffset = -Int(-Month(datDate) / cintSemiyearMonthCount) * cintSemiyearMonthCount
datFirstDate = DateSerial(Year(datDate), lngOffset + 1, 0)
intRows = cintYearSemiyearCount * cintYears
strFormat = ctl.Format
varValue = True               ' True to initialize.
Case acLBOpen
varValue = Timer              ' Autogenerated unique ID.
Case acLBGetRowCount            ' Get rows.
varValue = intRows            ' Set number of rows.
Case acLBGetColumnCount         ' Get columns.
varValue = 1                  ' Set number of columns.
Case acLBGetColumnWidth         ' Get column width.
varValue = -1                 ' Use default width.
Case acLBGetValue               ' Get the data for each row.
varValue = DateAdd("q", lngRow * cintYearSemiyearCount, datFirstDate)
Case acLBGetFormat              ' Format the data.
varValue = strFormat          ' Use format of control.
Case acLBEnd
' Do something when form with listbox closes or
' listbox is requeried.
End Select

' Return Value.
ListUltimoSemiyears = varValue

End Function

Public Function ListUltimoYears( _
ctl As Control, _
lngId As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) _
As Variant

' Period for listing dates.
Const cintYears               As Integer = 15

' 2014-09-24. Cactus Data ApS, CPH.

Static datFirstDate   As Date
Static strFormat      As String
Static intRows        As Integer

Dim datDate           As Date
Dim varValue          As Variant

Select Case intCode
Case acLBInitialize
datDate = Date
datFirstDate = DateSerial(Year(datDate), 12, 31)
intRows = 1 + cintYears
strFormat = ctl.Format
varValue = True               ' True to initialize.
Case acLBOpen
varValue = Timer              ' Autogenerated unique ID.
Case acLBGetRowCount            ' Get rows.
varValue = intRows            ' Set number of rows.
Case acLBGetColumnCount         ' Get columns.
varValue = 1                  ' Set number of columns.
Case acLBGetColumnWidth         ' Get column width.
varValue = -1                 ' Use default width.
Case acLBGetValue               ' Get the data for each row.
Case acLBGetFormat              ' Format the data.
varValue = strFormat          ' Use format of control.
Case acLBEnd
' Do something when form with listbox closes or
' listbox is requeried.
End Select

' Return Value.
ListUltimoYears = varValue

End Function
``````
/gustav
UltimoDates.accdb
0

LVL 48

Assisted Solution

Dale Fye (Access MVP) earned 166 total points
ID: 40341279
I would use a table (tbl_Numbers) which I put in my default database template.  This table contains a single field (intNumber) and only 10 records (the values 0-9).  With this table you can create a query that gives you the next 99 quarters.

SELECT DateAdd("q", Q.intNumber, #9/30/14#) as QtrEnd
FROM (
SELECT Tens.intNumber + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
) as Q
WHERE Q.intNumber < 61
ORDER BY SELECT DateAdd("q", intNumber, #9/30/14#) as QtrEnd
0

Author Comment

ID: 40341501
OK thank you for the responses.  I have to post a follow up because I am not sure how to use the function.

How do I list the quarter end dates?  I believe Rey has the answer as I can see it loops which makes me think that the code will list all quarter end dates but but I am not sure how exactly how I can show all of them?   Gustav's db shows all of the dates grouped into Years, MOnths in separate combo boxes but this is not exactly what I am after.
0

LVL 50

Expert Comment

ID: 40341505
> I need to display each of these dates.
> Gustav's db shows all of the dates grouped into Years, MOnths in separate combo boxes but
> this is not exactly what I am after.

So what are you after?

/gustav
0

Author Comment

ID: 40341511
fyi:  I am not wanting to select any of the dates but rather only list them.  Much like in excel when you type in a date and use an EOMonth function and drag it out over X number of years.
0

Author Comment

ID: 40341516
didnt see your response Gustav.  Let me know if after reading my fyi response above if it is more clear what I am after.
0

LVL 50

Expert Comment

ID: 40341521
But this is not Excel. We have tables and queries, listboxes and comboboxes.

Perhaps you could elaborate on the purpose?

/gustav
0

Author Comment

ID: 40341524
09/30/14      12/31/14      03/31/15      06/30/15      09/30/15      12/31/15      03/31/16      06/30/16      09/30/16      12/31/16      03/31/17      06/30/17      09/30/17      12/31/17      03/31/18      06/30/18      09/30/18      12/31/18

A listing like the above.  This is a paste from excel.  I am wanting to show in a form those dates maybe across top.
0

LVL 50

Expert Comment

ID: 40341525
OK.

/gustav
0

LVL 120

Expert Comment

ID: 40341538
< I am wanting to show in a form those dates maybe across top. >
what type of form do you have?
do you want to show them as caption to labels?

better upload a db with the form.
0

LVL 50

Expert Comment

ID: 40341540
That could be done as a concatenated string or by filling labels or tabs. Or column headers.
Once listed, what is the user supposed to do?

/gustav
0

Author Comment

ID: 40341548
OK will give a little more info but I thought keeping it simple would be best.  I have a list of compliance items, which are grouped by Year, Quarter, SemiAnnually, Monthly.  In each of those groupings there are a few items to fulfill.  Example:  at the end of the year I am suppose to submit financial statements within 120 days of year end.  Every quarter I have a list of FEES I need to pay.  Every month I have environmental reports to submit.  There are many many items to track.  I also wold have a checkbox to each to note whether or not completed.  All of these items I need to keep track of for the next 15 years.  Each I need to see what is coming due for the end of month, end of quarter etc etc.
0

Author Comment

ID: 40341567
OK I have something but it is not much as of right now and might only provide a little help.  I have just started on this project.  Maybe after opening the attached you might have a better idea of what I am after.
EE.accdb
0

Author Comment

ID: 40341578
In the attached just imagine the table as being populated with 1000 records and each field is to be fulfilled either Annually, SemiAnnually, Quarterly, MOnthly over 15 years.
0

LVL 50

Expert Comment

ID: 40341587
You will need a table holding these tasks and a indication of their interval in months and a table holding the log.

Then you can run a query that calculates the previous date and the next date.

Whenever a task is done, add a record to the log table with the task ID and the created date.

Now you can join the two tables and filter on tasks done and tasks coming up.

The comboboxes in my demo should be reversed to list dates back in time for selection of tasks done.

/gustav
0

LVL 50

Expert Comment

ID: 40341682
I see. It's a bit more complicated. Some reports only should run a few times, some will not run from the beginning of the project. You will need a start date and an end date and/or a count of times the report should run.

Still, I would not create tables with future dates - these can easily be calculated - and could very well change in the next 15 years. Even the count of 15 years may change. No one knows.
I would only record those reports/tasks actually done or published. That will not change. Thus, each record will represent a completed task/report.

1. To list upcoming or overdue tasks.
2. To list all finished tasks.

A third task will be to maintain the future list of upcoming tasks/report. And once run, a report should not be able to be deleted, only deactivated for future runs.

/gustav
0

Author Comment

ID: 40341899
OK I see that my initial explanation was not adequate enough.  Sorry.  Please keep in mind I am far from expert and while easy for you trying to explain can be challenging sometimes when I venture into the expert techy stuff.  I confess that maybe I was wanting to do what is best for excel.

Maybe I can just split the points?  Rey answered first and would assign best answer to Rey (i believe its necessary to assign a best answer but not sure).  Any objections let me know.
0

LVL 50

Expert Comment

ID: 40341919
No problem. You should really do as you find it best.

/gustav
0

Author Closing Comment

ID: 40342029
thanks again.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form openâ€¦
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Usâ€¦
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
Suggested Courses
Course of the Month6 days, 22 hours left to enroll