如何在MS Access Queries中格式化日期以防止美国/英国问题 [英] How do you format dates within MS Access Queries to prevent the US/UK issue

问题描述

如何确保在过滤访问查询中的日期时选择正确数量的记录：

How do I ensure that I pick up the right number of records when filtering for dates within an Access Query:

SELECT ID, REF, SalesDate, DCount("ID","tblRecords"," Ref='" & [Ref] & "' AND [SalesDate]=#" & format([SalesDate],"yyyy/mm/dd") & "#") as EXPR1 from tblCurrent 

它拾取日期ok如果不能被误解为28-04-12，但如果是04-06-12它不会接受，因为它假设是错误的方式。

It picks up the date ok if it cannot be misconstrued such as 28-04-12, but if it is 04-06-12 it doesn't pick it up as it's assuming it's the wrong way around.

请注意，此查询不是即时创建的，也不能从表单等生成。

Note that this query is not created on the fly or generated from a form etc...

推荐答案

我使用yyyy / mm / dd在VBA中的日期：

I either use yyyy/mm/dd for dates in VBA:

#" & Format([SalesDate],"yyyy/mm/dd") & "#"

或参数，用于构建查询。

Or parameters, for building queries.

编辑附加信息

看到你在使用SQL Server，我建议你使用派生表，哪个您可能会发现更快，例如：

Seeing you are using SQL server, I suggest you use a derived table, which you may find faster, for example:

SELECT dbo_Table_1.ADate, ACount FROM dbo_Table_1
LEFT JOIN (SELECT a.ADate,Count(*) As ACount 
           FROM  dbo_Table_1 As a GROUP BY a.ADate) b
ON dbo_Table_1.Adate=b.ADate

编辑讨论

SELECT * FROM dbo_vwRecordsCurrent As t 
LEFT JOIN (
   SELECT a.OpptyIncentiveModifiedDate, a.DataSetID, Count(*) AS ACount 
   FROM dbo_vwRecordsHistorical AS a 
   WHERE a.OpportunityIgnored = True 
   GROUP BY a.OpptyIncentiveModifiedDate, a.DataSetID) AS h 
ON t.OpptyIncentiveModifiedDate = h.OpptyIncentiveModifiedDate 
AND t.DataSetID = h.DataSetID

我的名字很长，我的别名是别名，所以对我来说，使用外部sql的别名更可读。它们在内部的sql中是必不可少的。将派生表与现有表的名称进行别名并不是一个好主意。

I have aliased your tables as the names are very long, so to me, it is more readable to use aliases on the outer sql. They are essential in the inner sql. It is not a good idea to alias a derived table with the name of an existing table.

这篇关于如何在MS Access Queries中格式化日期以防止美国/英国问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！