Union Nulls。为什么4/14/2004低于4/2/2004？ [英] Union Nulls. Why is 4/14/2004 less than 4/2/2004?

问题描述

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.

推荐答案

Salad写道：

Salad wrote:

A97。

情况：我有3个表，每个表都有一个文本字段，前两个表中有一个日期字段


表1 Text1，Date1
表2 Text2，Date2 <表3 Text3（无日期字段）

以下组成一个名为Query1的已保存查询
选择Text1作为TF，Date1作为DF从表1
UNION ALL
选择Text2作为TF，Date2作为DF来自表2
UNION ALL
选择Text3作为TF，Null作为DF来自表3

现在如果我运行查询从Query1中选择记录
选择*来自Query1其中DF<日期（）
或使用Query1作为具有类似过滤器的表单中的记录集，显示日期大于今天的记录。我已多次检查
Access认为4/2/2004大于2004年4月14日。例如：
Expr1：DF<日期（）
标准正确
果然。 4/15/2004的记录显示出来。

如果我删除该行
选择Text3 As TF，Null As DF来自Table3
那么查询就像广告一样工作。创建Null列是罪魁祸首。

这个问题在Google中很常见......很多人建议使用
CDate，这是无用的。格式化毫无价值。除非你实际运行类似的查询来查看结果，否则你不会相信访问会让人感到困惑。

我唯一能够解决的问题解决这个问题的关键是创建第二个查询并使用复杂的逻辑。我打电话给查询查询2.

选择TF，IIF（IsDate（DF），DateSerial（年（DF），_月/月（DF），日（DF）） ，Null）

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-----

问题是当JET可以''确定列的单个数据类型，

它会自动将其转换为字符串。 Null不表示数据类型，

因此DF变为字符串。随后，DF的联合也变成了一个

字符串。


解决方案？如果IIf你可以在只有2个参数的
查询中使用，那么有一个未记录的表格。 JET非常聪明，可以使列使用由段号2指示的

数据类型，即使在
参数1为False时IIf返回Null。


所以，reformulatd联合查询看起来像这样...


SELECT

Text1作为TF，

Date1作为DF

来自Table1

UNION ALL SELECT

Text2作为TF，

Date2作为DF

FROM Table2

UNION ALL SELECT

Text3作为TF，

IIf（假，＃1/1/2000 ＃）作为DF

来自表3

IIf（False，＃1/1/2000＃）生成一个包含所有Null值的日期列。你可以用其他数据类型做同样的事情，例如

IIf（假，CLng（0））

IIf（假，CCur （0））


2004年4月3日星期六06:13:33 GMT，Salad< oi*@vinegar.com>写道：

The problem is that when JET can''t determine a single data type for a column,
it automatically converts it to a string. Null does not indicate a data type,
so Null as DF becomes a string. Subsequently, the union of DF becomes a
string as well.

The solution? There is an undocumented form if IIf that you can use in
queries with only 2 arguments. JET is smart enough to make the column use the
data type indicated by argment number 2, even though IIf returns Null when
argument 1 is False.

So, the reformulatd union query looks like this...

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,
IIf(False,#1/1/2000#) As DF
FROM Table3
IIf(False,#1/1/2000#) generates a date column with all Null values. You can
do the same thing with other data types, e.g.
IIf(False,CLng(0))
IIf(False,CCur(0))

On Sat, 03 Apr 2004 06:13:33 GMT, Salad <oi*@vinegar.com> wrote:

A97。

情况：我有3个表，每个表都有一个文本字段，前两个表中有一个日期字段
：

Table1 Text1，Date1
Table2 Text2，Date2
Table3 Text3（无日期字段）

以下组成一个名为Query1 UNION ALL
选择Text2作为TF，Date2作为DF从表2
UNION ALL
选择Text3作为TF，Null作为DF从表3开始

现在如果我运行查询从Query1中选择记录
选择*来自Query1其中DF<日期（）
或使用Query1作为具有类似过滤器的表单中的记录集，显示日期大于今天的记录。我已多次检查
Access认为4/2/2004大于2004年4月14日。例如：
Expr1：DF<日期（）
标准正确
果然。 4/15/2004的记录显示出来。

如果我删除该行
选择Text3 As TF，Null As DF来自Table3
那么查询就像广告一样工作。创建Null列是罪魁祸首。

这个问题在Google中很常见......很多人建议使用
CDate，这是无用的。格式化毫无价值。除非你实际运行类似的查询来查看结果，否则你不会相信访问会让人感到困惑。

我唯一能够解决的问题解决这个问题的关键是创建第二个查询并使用复杂的逻辑。我打电话给查询查询2.

选择TF，IIF（IsDate（DF），DateSerial（年（DF），_月/月（DF），日（DF）） ，Null）

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.

MGFoster写道：

MGFoster wrote:

Salad写道：

Salad wrote:

A97。

情况：我有3个表格，每个表格都有一个文本字段，前两个表格中有一个日期字段

Table1 Text1，Date1
Table2 Text2，Date2
Table3 Text3（无日期字段）

以下组成一个名为Query1的已保存查询
选择Text1 As TF，Date1作为DF来自表1
UNION ALL
选择Text2作为TF，Date2作为DF来自表2
UNION ALL
选择Text3作为TF，Null作为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，这是无用的。格式化毫无价值。除非您实际运行类似的查询以查看结果，否则您不会相信Access会让您感到困惑。

我唯一能够解决的问题解决这个问题的关键是创建第二个查询并使用复杂的逻辑。我打电话给查询查询2.

选择TF，IIF（IsDate（DF），DateSerial（年（DF），_月/月（DF），日（DF）） ，Null）

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.

-----开始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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！