• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

Date progression over many years

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
pdvsa
Asked:
pdvsa
  • 10
  • 8
  • 2
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
pdvsaProject financeAuthor Commented:
I will test this in the am.  Thank you for the response.
0
 
Gustav BrockCIOCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Gustav BrockCIOCommented:
> 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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Gustav BrockCIOCommented:
But this is not Excel. We have tables and queries, listboxes and comboboxes.

Perhaps you could elaborate on the purpose?

/gustav
0
 
pdvsaProject financeAuthor Commented:
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
 
Gustav BrockCIOCommented:
OK.

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
< 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
 
Gustav BrockCIOCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Gustav BrockCIOCommented:
No problem. You should really do as you find it best.

/gustav
0
 
pdvsaProject financeAuthor Commented:
thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now