Solved

Date progression over many years

Posted on 2014-09-23
21
130 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
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 167 total points
Comment Utility
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
Comment Utility
I will test this in the am.  Thank you for the response.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
Comment Utility
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
Comment Utility
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
Comment Utility
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 49

Expert Comment

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

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
OK.

/gustav
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
< 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 49

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

/gustav
0
 

Author Closing Comment

by:pdvsa
Comment Utility
thanks again.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now