Solved

Date progression over many years

Posted on 2014-09-23
21
140 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 120

Accepted Solution

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

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

Assisted Solution

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

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
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

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

730 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