CODEWITHSUNDEEP
sqlms-access
Jake Duddy
Jake Duddy

Reputation: 36

UNION data type mismatch

Running a UNION query in an access database. I've defined every variable as a int since there was a data type mismatch error prompt. This has not resolved the issue. Each of the variables have values of either 1 or 0 and no nulls. Any ideas? 

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;

Upvotes: 1

Views: 2917

Answers (2)

Rominus
Rominus

Reputation: 1221

The one thing I can think of is that doing the CInt() conversion during the UNION may be screwing things up. I'd try doing the conversion in subqueries before doing the UNION. Something like:

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

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

Check you don't have any nulls in any of the columns.

Access SQL is a little strange when it comes to nulls (noting that Standard SQL nulls are strange to begin with!). For example you can't cast a null to a data type:

SELECT DISTINCT CINT( NULL ) AS null_cast_to_int FROM AnyPopulatedTable;

errors with "Invalid use of Null".

So all Access SQL nulls are of the same type but what type?:

SELECT DISTINCT TYPENAME ( NULL ) AS type_name FROM  AnyPopulatedTable;

does not error and returns 'Null' !!

Upvotes: 1

Related Questions