UNION数据类型不匹配

Question

在access数据库中运行联合查询。我已经将每个变量定义为int类型，因为有一个数据类型不匹配错误提示。这并没有解决问题。每个变量的值要么为1，要么为0，并且没有空值。有什么想法吗？

SELECT
CInt(qryGB.BM∞) AS [BM∞],
CInt(qryGB.PM∞) AS [PM∞],
CInt(qryGB.P∞) AS [P∞],
CInt(qryGB.[RAG_B<0]) AS [RAG_B<0],
CInt(qryGB.[RAG_P<0]) AS [RAG_P<0],
CInt(qryGB.[RAG_C<0]) AS [RAG_C<0],
CInt(qryGB.[B<0]) AS [B<0],
CInt(qryGB.[P<0]) AS [P<0],
CInt(qryGB.[C<0]) AS [C<0],
CInt(qryGB.[P-1]) AS [P-1],
CInt(qryGB.[C-1]) AS [C-1],
CInt(qryGB.P0) AS [P0],
CInt(qryGB.C0) AS [C0],
CInt(qryGB.[P+1]) AS [P+1],
CInt(qryGB.[P+2]) AS [P+2],
CInt(qryGB.[P+3]) AS [P+3]
FROM qryGB

UNION ALL SELECT
CInt(qryTMD.BM∞) AS [BM∞],
CInt(qryTMD.PM∞) AS [PM∞],
CInt(qryTMD.P∞) AS [P∞],
CInt(qryTMD.[RAG_B<0]) AS [RAG_B<0],
CInt(qryTMD.[RAG_P<0]) AS [RAG_P<0],
CInt(qryTMD.[RAG_C<0]) AS [RAG_C<0],
CInt(qryTMD.[B<0]) AS [B<0],
CInt(qryTMD.[P<0]) AS [P<0],
CInt(qryTMD.[C<0]) AS [C<0],
CInt(qryTMD.[P-1]) AS [P-1],
CInt(qryTMD.[C-1]) AS [C-1],
CInt(qryTMD.P0) AS [P0],
CInt(qryTMD.C0) AS [C0],
CInt(qryTMD.[P+1]) AS [P+1],
CInt(qryTMD.[P+2]) AS [P+2],
CInt(qryTMD.[P+3]) AS [P+3]
FROM qryTMD;

Answer 1

检查您在任何列中都没有任何空值。

当涉及到空值时，Access SQL有点奇怪(请注意，标准SQL空值从一开始就很奇怪！)例如，您不能将null转换为数据类型：

SELECT DISTINCT CINT( NULL ) AS null_cast_to_int FROM AnyPopulatedTable;

出现"Invalid use of Null“错误。

那么所有的Access SQL空值都是同一类型的，但是什么类型呢？：

SELECT DISTINCT TYPENAME ( NULL ) AS type_name FROM  AnyPopulatedTable;

不出错并返回'Null‘!！

Answer 2

我能想到的一件事是，在UNION期间进行CInt()转换可能会把事情搞砸。在执行UNION之前，我会尝试在子查询中进行转换。类似于：

SELECT
a.[BM∞],
a.[PM∞],
a.[P∞],
a.[RAG_B<0],
a.[RAG_P<0],
a.[RAG_C<0],
a.[B<0],
a.[P<0],
a.[C<0],
a.[P-1],
a.[C-1],
a.[P0],
a.[C0],
a.[P+1],
a.[P+2],
a.[P+3]
FROM
    (SELECT
    CInt(qryGB.[BM∞]) AS [BM∞],
    CInt(qryGB.[PM∞]) AS [PM∞],
    CInt(qryGB.[P∞]) AS [P∞],
    CInt(qryGB.[RAG_B<0]) AS [RAG_B<0],
    CInt(qryGB.[RAG_P<0]) AS [RAG_P<0],
    CInt(qryGB.[RAG_C<0]) AS [RAG_C<0],
    CInt(qryGB.[B<0]) AS [B<0],
    CInt(qryGB.[P<0]) AS [P<0],
    CInt(qryGB.[C<0]) AS [C<0],
    CInt(qryGB.[P-1]) AS [P-1],
    CInt(qryGB.[C-1]) AS [C-1],
    CInt(qryGB.[P0]) AS [P0],
    CInt(qryGB.[C0]) AS [C0],
    CInt(qryGB.[P+1]) AS [P+1],
    CInt(qryGB.[P+2]) AS [P+2],
    CInt(qryGB.[P+3]) AS [P+3]
    FROM qryGB) as a
UNION ALL SELECT
b.[BM∞],
b.[PM∞],
b.[P∞],
b.[RAG_B<0],
b.[RAG_P<0],
b.[RAG_C<0],
b.[B<0],
b.[P<0],
b.[C<0],
b.[P-1],
b.[C-1],
b.[P0],
b.[C0],
b.[P+1],
b.[P+2],
b.[P+3]
FROM
    (SELECT
    CInt(qryTMD.[BM∞]) AS [BM∞],
    CInt(qryTMD.[PM∞]) AS [PM∞],
    CInt(qryTMD.[P∞]) AS [P∞],
    CInt(qryTMD.[RAG_B<0]) AS [RAG_B<0],
    CInt(qryTMD.[RAG_P<0]) AS [RAG_P<0],
    CInt(qryTMD.[RAG_C<0]) AS [RAG_C<0],
    CInt(qryTMD.[B<0]) AS [B<0],
    CInt(qryTMD.[P<0]) AS [P<0],
    CInt(qryTMD.[C<0]) AS [C<0],
    CInt(qryTMD.[P-1]) AS [P-1],
    CInt(qryTMD.[C-1]) AS [C-1],
    CInt(qryTMD.[P0]) AS [P0],
    CInt(qryTMD.[C0]) AS [C0],
    CInt(qryTMD.[P+1]) AS [P+1],
    CInt(qryTMD.[P+2]) AS [P+2],
    CInt(qryTMD.[P+3]) AS [P+3]
    FROM qryTMD) as b