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

问题描述

我在 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 查询中创建日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！