在 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]

带参数运行:

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

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