Create a date table between 2 dates

Question

I am a newbie in programming and trying to add some automation in my team to help with daily operation.

I try to create a function to create interest payment schedule according to given start date, end date and intervals. For example, for a one-year security with start date 2017/01/14, maturity date 2018/01/14, payment frequency is every 3 months. it has 4 interest period: 2017/01/14 - 2017/04/14, 2017/04/14 - 2014/07/14, 2017/07/14 - 2017/10/14, 2017/10/14 - 2018/01/14. I want to create a date table to display these 4 periods in Access.

The record should look like below:

seq startdate enddate

1       2017/01/14  2017/04/14
2       2017/04/14  2017/07/14
3       2017/07/14  2017/10/14
4       2017/10/14  2018/01/14

Could anyone help me with this? Thanks a lot.

Welcome to StackOverflow! Please see this page on how to ask a good question: https://stackoverflow.com/help/how-to-ask. We're not a code-writing service, so let us know what you've tried so far and where you're stuck. — Arya McCarthy, Apr 10 '17 at 02:48

score 0 · Answer 1 · answered Apr 10 '17 at 07:00

You can use the MSysObjects table and a Cartesian query to create this:

PARAMETERS 
    Period Text ( 255 ), 
    Periods Short, 
    FirstDate DateTime;
SELECT DISTINCT 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1 As Sequence,
    DateAdd([Period],[Sequence]-1,[FirstDate]) AS [DateStart],
    DateAdd([Period],[Sequence],[FirstDate]) AS [DateEnd]
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca
WHERE 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<[Periods]

Run this with the parameters:

Period: q
Periods: 4
FirstDate: 2017-04-14

Create a date table between 2 dates

1 Answers1

Linked