在 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屋!