问题描述
A97。
情况：我有3个表格，每个表格都有一个文本字段，前两个表格中有一个日期字段
：
表1 Text1，Date1
表2 Text2，Date2
表3 Text3（无日期字段）
以下组成一个名为Query1的已保存查询
选择Text1作为TF，Date1作为DF从表1
UNION ALL
选择Text2 As TF，Date2作为DF来自表2
UNION ALL
选择Text3作为TF，Null As DF来自表3
现在如果我运行查询从Query1中选择记录
选择*来自Query1其中DF<日期（）
或使用Query1作为具有类似过滤器的表单中的记录集，显示日期大于今天的记录
。我已多次检查
，Access认为4/2/2004大于4/14/2004。例如：
Expr1：DF<日期（）
标准真实
果然。 4/15/2004的记录显示。
如果我删除该行
选择Text3 As TF，Null As DF来自Table3
然后查询按照广告的方式工作。创建Null列是罪魁祸首。
这个问题在Google中很常见......很多人建议使用
CDate，这是没用的。格式化毫无价值。除非你实际运行类似的查询以查看结果，否则你不会相信
访问会让人感到困惑。
我能够解决这个问题的唯一解决方案是创建第二个查询并使用复杂的逻辑。我打电话给
查询Query2。
选择TF，IIF（IsDate（DF），DateSerial（年（DF），_
月（DF），日（DF）），空）
IOW，您需要检查日期字段是否为日期然后转换
如果日期是约会的日期。
如果您对Query1的结果有解释，我会很感激。我认为
假设日期在运行时被视为变体。
是否有其他方法可以创建空白日期（不在表格中，但是在
查询中）避免这种情况？我试过DateSerial（0,0,0）但是
返回11/30/1999。去图。
A97.
Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:
Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)
The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3
Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many times
that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.
If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the culprit.
This problem is common enough in Google...lots of people recommend using
CDate which is useless. Formatting is worthless. And unless you
actually run a similar query to see the results, you wouldn''t believe
Access would be this confused.
The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.
Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)
IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.
If you have an explanation for Query1''s results I''d appreciate it. I
suppose that the Dates are cast as Variants when it is run.
Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but that
returned 11/30/1999. Go figure.
推荐答案
----- BEGIN PGP签名消息-----
哈希：SHA1
奇怪。看起来日期列正在变成一个字符串
数据类型。也许如果您将＃12/30/1899＃替换为NULL，则日期
列将保留为日期列。但是，那么你必须将你的
标准改为
WHERE DF<> ＃12/30/1899＃AND DF<日期（）
而不是
其中DF<日期（）
- -
MGFoster ::: mgf00< at> earthlink< decimal-point> net
奥克兰，加利福尼亚州（美国）
-----开始PGP SIGNATURE -----
版本： PGP个人隐私5.0
Charset：noconv
iQA / AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
= YYXS
-----结束PGP SIGNATURE -----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Strange. It looks like the date column is being turned into a string
data type. Perhaps if you put #12/30/1899# instead of NULL the date
column will remain a date column. But, then you''ll have to change your
criteria to
WHERE DF <> #12/30/1899# AND DF < Date()
instead of
Where DF < Date()
- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
=YYXS
-----END PGP SIGNATURE-----
-----开始PGP签名的消息-----
哈希：SHA1
奇怪。看起来日期列正在变成一个字符串
数据类型。也许如果你把＃12/30/1899＃而不是NULL，那么日期
列将保持日期列。但是，那么你必须将你的
标准改为
WHERE DF<> ＃12/30/1899＃AND DF<日期（）
代替
DF<日期（）
- -
MGFoster ::: mgf00< at> earthlink< decimal-point> net
奥克兰，加利福尼亚州（美国）
-----开始PGP签名-----
版本：PGP个人隐私5.0
Charset：noconv
iQA / AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
= YYXS
----- END PGP SIGNATURE -----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Strange. It looks like the date column is being turned into a string
data type. Perhaps if you put #12/30/1899# instead of NULL the date
column will remain a date column. But, then you''ll have to change your
criteria to
WHERE DF <> #12/30/1899# AND DF < Date()
instead of
Where DF < Date()
- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
=YYXS
-----END PGP SIGNATURE-----
谢谢MG。在大多数情况下，您的解决方案可行不幸的是
还有另一个日期字段（我使用的是一个简单的例子）我还要求b $ b过滤并使用这种方法会排除一些会记录的日期
需要显示。如果我解决了这个问题，那么1899年的日期将会使用户感到困惑。我相信你是正确的，它是
转换为字符串。这是唯一有意义的事情....我保持
在日期条款中思考但是4/2将大于字符串
" 4/14" ...感谢您指出。
Thanks MG. In most instances your solution would work. Unfortunately
there is another date field (I was using a simple example) that I also
filter on and using this method would exclude some records that would
need to be displayed. If I worked around that, a date from 1899 would
confuse the users. I do believe you are correct in correct that it is
converted to string. It''s the only thing that makes sense....I kept
thinking in date terms but "4/2" would be greater than the string
"4/14"...thanks for pointing that out.
这篇关于Union Nulls。为什么4/14/2004低于4/2/2004？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！