在2个日期之间创建日期表 [英] Create a date table between 2 dates

问题描述

我是编程方面的新手，并且试图在团队中增加一些自动化以帮助日常操作。

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

我尝试创建一个函数来根据给定的开始日期，结束日期和间隔创建利息支付时间表。例如，对于开始日期为2017/01/14，到期日期为2018/01/14的一年期证券，支付频率为每3个月一次。它有4个利息期：2017/01/14-2017/04/14，2017/04/14-2014/07/14，2017/07/14-2017/10/14，2017/10/14-2018 / 01/14。我想创建一个日期表以在Access中显示这4个期间。

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.

记录应如下所示：

seq startdate enddate

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.

推荐答案

您可以使用 MSysObjects 表和笛卡尔查询来创建此表：

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

这篇关于在2个日期之间创建日期表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！