UNION Query

[Morris.C]

Has anyone experienced a UNION Query that works in Access97, but returns a
'Data type mismatch in criteria expression' when used in Access2003?

Thanks.

 

[John Vinson]

Has anyone experienced a UNION Query that works in Access97, but returns a
'Data type mismatch in criteria expression' when used in Access2003?

Thanks.

No, but I'd be interested in seeing one and willing to test it for
you.

John W. Vinson[MVP]

 

[Morris.C]

No, but I'd be interested in seeing one and willing to test it for
you.

John W. Vinson[MVP]

This is the UNION Query that works OK with A97, but fails with the 'type
mismatch' error in A2003.
I'm assuming that 'type mismatch' means that the UNION 'point' have
different data types. But then A97 would have the same problem!

SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], Null AS DCNType, Null AS nDCN02,
Null AS nDCN

FROM (tbl_ECR LEFT JOIN qry_Junction_ES_01 ON tbl_ECR.CRNumberID =
qry_Junction_ES_01.Junction_ES_04)

LEFT JOIN
qry_Junction_EX_01 ON tbl_ECR.CRNumberID = qry_Junction_EX_
01.Junction_EX_04

WHERE (((qry_Junction_ES_01.Junction_ES_04) Is Null) AND
((qry_Junction_EX_01.Junction_EX_04) Is Null));

UNION
SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], IIf([SDCN]="S--","","S") AS
DCNType, qry_Junction_ES_SDCN_01.SDCN02, IIf([SDCN]="S--","",[SDCN]) AS
nDCN

FROM tbl_ECR INNER JOIN qry_Junction_ES_SDCN_01 ON tbl_ECR.CRNumberID =
qry_Junction_ES_SDCN_01.Junction_ES_04;

UNION
SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], IIf([XDCN]="X--","","X") AS
DCNType, qry_Junction_EX_XDCN_01.XDCN02, IIf([XDCN]="X--","",[XDCN]) AS
nDCN

FROM tbl_ECR INNER JOIN qry_Junction_EX_XDCN_01 ON tbl_ECR.CRNumberID =
qry_Junction_EX_XDCN_01.Junction_EX_04;

 

[John Vinson]

This is the UNION Query that works OK with A97, but fails with the 'type
mismatch' error in A2003.

Well, without having the queries which constitute it I can't test it
of course. Do each of THEM work correctly? The Type Mismatch would
(typically) refer to a critera expression rather than an error in the
UNION itself. But let's line things up and check:

SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], Null AS DCNType, Null AS nDCN02,
Null AS nDCN
....
UNION
SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], IIf([SDCN]="S--","","S") AS
DCNType, qry_Junction_ES_SDCN_01.SDCN02, IIf([SDCN]="S--","",[SDCN]) AS
nDCN
...
SELECT tbl_ECR.CRNumberID, tbl_ECR.[_CRNumber], tbl_ECR.[_Summary],
tbl_ECR.Requestor, tbl_ECR.[_RequestorsPriority], tbl_ECR.
[_CRBCalssification], tbl_ECR.[_Status], IIf([XDCN]="X--","","X") AS
DCNType, qry_Junction_EX_XDCN_01.XDCN02, IIf([XDCN]="X--","",[XDCN]) AS
nDCN

I would GUESS that one thing to try might be to use "" instead of NULL
as the value of DCNType and nDCN in the first query, since these
fields are forced to Text type in the other two, or (flipside) to use
NULL instead of "" as the True argument in all four IIF() calls.

John W. Vinson[MVP]

 

[Morris.C]

I would GUESS that one thing to try might be to use "" instead of NULL
as the value of DCNType and nDCN in the first query, since these
fields are forced to Text type in the other two, or (flipside) to use
NULL instead of "" as the True argument in all four IIF() calls.

John W. Vinson[MVP]

It seems that you came up with the same idea I had. (It probably took you
a few minutes to come up with this...It took me a whole damn day!!!)
It seems that the second 'empty' column, ie nDCN02, is a number in its
originating table. When I changed it to <"" AS nDCN02> the type mismatch
disappeared.
This of course begs the question:
Why does a column set to 'Null' not like being populated with an Integer?

Thanks for the help.

 

[John Vinson]

Why does a column set to 'Null' not like being populated with an Integer?

The IIF will return a value of the "most general" datatype of either
of its return arguments. If they're both Text - "" and a string - it
will return a Text datatype; if either is NULL it will return a
Variant.

You can put an Integer or a Text value into a Variant - but not vice
versa!

John W. Vinson[MVP]