无法使用短日期筛选MS访问日期时间字段 [英] Can't filter MS access datetime field using short date

问题描述

我正在尝试为程序生成水晶报表，显然，如果我使用 =而不是>或<，则无法过滤那些日期时间格式的数据。
我的代码是这样的

I am trying to generate a crystal report for my program, apparently I wasn't able to filter those data which is in datetime format if i use "=" and not "> or <". my code goes something like this

dim x as string ="1/1/2014"
dim y as date
y=cdate(x)

select from [tblname] WHERE [datetime field]=#" & y &"#

假设2014年1月1日之前有多个记录，则查询不会产生任何记录，除非我复制了实际的datetime数据存储在访问中或使用>或<

the query won't yield any record assuming there is more than one record with 1/1/2014 date not unless i copy the actual datetime data stored in access or use the "> or <"

有什么方法可以在SQL查询中将datetime字段格式化为短日期以便于比较？因为我认为存储的时间是我无法使用 =

is there any way that i can format in my SQL query the datetime field to short date for easy comparison? because i believe the time stored is the reason why i can't filter it using "="

推荐答案

进行过滤的原因，参数查询。但是由于我不知道如何或是否可以对Crystal Reports使用Access参数查询，因此建议您将日期值的格式设置为＃yyyy-md＃。

This would be better as a parameter query. But since I don't know how or if you can use an Access parameter query with Crystal Reports, I'll suggest you format the date value as #yyyy-m-d#.

"select from [tblname] WHERE [datetime field]=" & Format(y, "\#yyyy-m-d\#")

如果要忽略将 [datetime字段] 与日期 y 匹配的一天中的时间，可以使用 DateValue（ [datetime字段]）会给您该日期的午夜。

If you want to ignore time of day when matching [datetime field] to day y, you can use DateValue([datetime field]) which gives you midnight of that date.

"select from [tblname] WHERE DateValue([datetime field])=" & Format(y, "\#yyyy-m-d\#")

但是，如果您的表包含许多行，您不想为每行评估 DateValue 。相反，您可以执行以下操作，并且如果索引 [datetime字段] ，它可以利用索引检索来提高性能。

However, if your table contains many rows, you don't want to evaluate DateValue for every row. Instead you can do something like this, and it can utilize indexed retrieval for faster performance if [datetime field] is indexed.

"select from [tblname] WHERE [datetime field]>=" & Format(y, "\#yyyy-m-d\#") & " AND [datetime field]<" & Format(y + 1, "\#yyyy-m-d\#")

这篇关于无法使用短日期筛选MS访问日期时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！