?
Solved

Date progression over many years

Posted on 2014-09-23
21
Medium Priority
?
143 Views
Last Modified: 2014-09-24
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
Comment
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
  • Learn & ask questions
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 668 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

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

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 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.
      varValue = DateAdd("q", lngRow, 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.
  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.
      varValue = DateAdd("m", lngRow, 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.
  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.
      varValue = DateAdd("yyyy", lngRow, 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.
  ListUltimoYears = varValue

End Function

Open in new window

/gustav
UltimoDates.accdb
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 664 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

by:pdvsa
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 51

Expert Comment

by:Gustav Brock
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

by:pdvsa
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

by:pdvsa
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 51

Expert Comment

by:Gustav Brock
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

by:pdvsa
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 51

Expert Comment

by:Gustav Brock
ID: 40341525
OK.

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 51

Expert Comment

by:Gustav Brock
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

by:pdvsa
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

by:pdvsa
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

by:pdvsa
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 51

Expert Comment

by:Gustav Brock
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 51

Expert Comment

by:Gustav Brock
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.

Then your application can easily handle the two main tasks:

    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

by:pdvsa
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 51

Expert Comment

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

/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 40342029
thanks again.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question