在访问查询中创建日期列表 [英] Create a List of Dates in Access Query

问题描述

我正在使用Access进行MakeTable查询.我要这样做，以便要创建的表中的一个字段是按日期顺序从2015年1月20日到2015年11月30日的日期列表.如何在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);

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