问题描述
我在 Access 中有一个 MakeTable 查询正在处理.我想让正在创建的表中的一个字段是从 1/2/2015 到 11/30/2015 按时间顺序排列的日期列表.这是如何在 Access 查询设计视图中完成的?
I have a MakeTable query in Access that I am working on. I want to make it so that one of the fields in the table being created will be a list of dates in chronological order from 1/2/2015 to 11/30/2015. How is this done in Access query design view?
推荐答案
您可以创建查询:
SELECT DISTINCT
Abs([id] Mod 10) AS N
FROM
MsysObjects;
另存为qdxNumber10
现在使用笛卡尔连接(乘法)创建一个查询以生成您的日期:
Now create a query with a Cartesian Join (multiplying) to generate your dates:
SELECT
DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,#2/1/2015#) AS [Date]
FROM
qdxNumber10 AS qdxNumber10_0,
qdxNumber10 AS qdxNumber10_1,
qdxNumber10 AS qdxNumber10_2
WHERE
DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,#2/1/2015#) <= #11/30/2015#;
在创建表格时使用此作为源.
Use this as source when you create your table.
要生成当前年份的所有日期,请使用:
To generate all dates for the current year, use:
SELECT
DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,DateSerial(Year(Date()),1,1)) AS [Date]
FROM
qdxNumber10 AS qdxNumber10_0,
qdxNumber10 AS qdxNumber10_1,
qdxNumber10 AS qdxNumber10_2
WHERE
DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,DateSerial(Year(Date()),1,1)) <= DateSerial(Year(Date()),12,31);
或者直接使用DateSerial
:
SELECT
DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) AS [Date]
FROM
qdxNumber10 AS qdxNumber10_0,
qdxNumber10 AS qdxNumber10_1,
qdxNumber10 AS qdxNumber10_2
WHERE
DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) <= DateSerial(Year(Date()),12,31);
这篇关于在 Access 查询中创建日期列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,WP2