Error with UNION query

  • Thread starter Thread starter George Hutto
  • Start date Start date
G

George Hutto

Folks,

Access 2000 -- I'm getting an error with a union. The table formats are the
same, the queries are basically the same, yet when I put them together in a
UNION, I get "data type mismatch in criteria expression".

Table(s):
Company number long
Customer text
CustName text
TransType text
TransNbr text
TransDate date/time
NbrDays number long
Future currency
Current currency
Age1 currency
Age2 currency
Age3 currency
Age4 currency
Over currency
Total currency

The query is as follows:

SELECT DetailedData28.Company, DetailedData28.Customer,
DetailedData28.CustName, DetailedData28.TransDate, DetailedData28.TransNbr,
DetailedData28.Current, DetailedData28.Age1 AS 30, DetailedData28.Age2 AS
60, DetailedData28.Age3 AS 90,
[DetailedData28].[Age4]+[DetailedData28].[Over] AS [Over 90],
left([DetailedData28].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData28].[TransNbr],8)) AS invc_number
FROM DetailedData28
WHERE ((DetailedData28.TransNbr) is not null)
AND (((DetailedData28.Age1)>0)
OR ((DetailedData28.Age2)>0)
OR ((DetailedData28.Age3)>0)
OR (([DetailedData28].[Age4]+[DetailedData28].[Over])>0))
UNION SELECT DetailedData30.Company, DetailedData30.Customer,
DetailedData30.CustName, DetailedData30.TransDate, DetailedData30.TransNbr,
DetailedData30.Current, DetailedData30.Age1 AS 30, DetailedData30.Age2 AS
60, DetailedData30.Age3 AS 90,
[DetailedData30].[Age4]+[DetailedData30].[Over] AS [Over 90],
left([DetailedData30].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData30].[TransNbr],8)) AS invc_number
FROM DetailedData30
WHERE ((DetailedData30.TransNbr) is not null)
AND (((DetailedData30.Age1)>0)
OR ((DetailedData30.Age2)>0)
OR ((DetailedData30.Age3)>0)
OR (([DetailedData30].[Age4]+[DetailedData30].[Over])>0));

As I stated, if I break this into two separate queries they run fine, but I
get an error when UNION'ed. Any help would be appreciated. Also, if you
have any suggestions that would improve performance within the query I'd
appreciate it.

George
 
Try getting rid of all the "As ..." phrases in the second
part of the Union Query. JET will use only the Aliases on
the first part on the Union Query.

HTH
Van T. Dinh
MVP (Access)
 
So even on the calculated values in the second part I'd remove the AS
aliases? If I leave them in the first part of the union, will the unaliased
fields show up correctly?

Van T. Dinh said:
Try getting rid of all the "As ..." phrases in the second
part of the Union Query. JET will use only the Aliases on
the first part on the Union Query.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Folks,

Access 2000 -- I'm getting an error with a union. The table formats are the
same, the queries are basically the same, yet when I put them together in a
UNION, I get "data type mismatch in criteria expression".

Table(s):
Company number long
Customer text
CustName text
TransType text
TransNbr text
TransDate date/time
NbrDays number long
Future currency
Current currency
Age1 currency
Age2 currency
Age3 currency
Age4 currency
Over currency
Total currency

The query is as follows:

SELECT DetailedData28.Company, DetailedData28.Customer,
DetailedData28.CustName, DetailedData28.TransDate, DetailedData28.TransNbr,
DetailedData28.Current, DetailedData28.Age1 AS 30, DetailedData28.Age2 AS
60, DetailedData28.Age3 AS 90,
[DetailedData28].[Age4]+[DetailedData28].[Over] AS [Over 90],
left([DetailedData28].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData28].[TransNbr],8)) AS invc_number
FROM DetailedData28
WHERE ((DetailedData28.TransNbr) is not null)
AND (((DetailedData28.Age1)>0)
OR ((DetailedData28.Age2)>0)
OR ((DetailedData28.Age3)>0)
OR (([DetailedData28].[Age4]+[DetailedData28].[Over])>0))
UNION SELECT DetailedData30.Company, DetailedData30.Customer,
DetailedData30.CustName, DetailedData30.TransDate, DetailedData30.TransNbr,
DetailedData30.Current, DetailedData30.Age1 AS 30, DetailedData30.Age2 AS
60, DetailedData30.Age3 AS 90,
[DetailedData30].[Age4]+[DetailedData30].[Over] AS [Over 90],
left([DetailedData30].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData30].[TransNbr],8)) AS invc_number
FROM DetailedData30
WHERE ((DetailedData30.TransNbr) is not null)
AND (((DetailedData30.Age1)>0)
OR ((DetailedData30.Age2)>0)
OR ((DetailedData30.Age3)>0)
OR (([DetailedData30].[Age4]+[DetailedData30].[Over])
0));

As I stated, if I break this into two separate queries they run fine, but I
get an error when UNION'ed. Any help would be appreciated. Also, if you
have any suggestions that would improve performance within the query I'd
appreciate it.

George



.
Click to expand...
Click to expand...
 
Well, I tried it, but I still get the same error. Any futher suggestions?

Van T. Dinh said:
Try getting rid of all the "As ..." phrases in the second
part of the Union Query. JET will use only the Aliases on
the first part on the Union Query.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Folks,

Access 2000 -- I'm getting an error with a union. The table formats are the
same, the queries are basically the same, yet when I put them together in a
UNION, I get "data type mismatch in criteria expression".

Table(s):
Company number long
Customer text
CustName text
TransType text
TransNbr text
TransDate date/time
NbrDays number long
Future currency
Current currency
Age1 currency
Age2 currency
Age3 currency
Age4 currency
Over currency
Total currency

The query is as follows:

SELECT DetailedData28.Company, DetailedData28.Customer,
DetailedData28.CustName, DetailedData28.TransDate, DetailedData28.TransNbr,
DetailedData28.Current, DetailedData28.Age1 AS 30, DetailedData28.Age2 AS
60, DetailedData28.Age3 AS 90,
[DetailedData28].[Age4]+[DetailedData28].[Over] AS [Over 90],
left([DetailedData28].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData28].[TransNbr],8)) AS invc_number
FROM DetailedData28
WHERE ((DetailedData28.TransNbr) is not null)
AND (((DetailedData28.Age1)>0)
OR ((DetailedData28.Age2)>0)
OR ((DetailedData28.Age3)>0)
OR (([DetailedData28].[Age4]+[DetailedData28].[Over])>0))
UNION SELECT DetailedData30.Company, DetailedData30.Customer,
DetailedData30.CustName, DetailedData30.TransDate, DetailedData30.TransNbr,
DetailedData30.Current, DetailedData30.Age1 AS 30, DetailedData30.Age2 AS
60, DetailedData30.Age3 AS 90,
[DetailedData30].[Age4]+[DetailedData30].[Over] AS [Over 90],
left([DetailedData30].[TransNbr], 2) AS invc_prefix,
CLng(Right([DetailedData30].[TransNbr],8)) AS invc_number
FROM DetailedData30
WHERE ((DetailedData30.TransNbr) is not null)
AND (((DetailedData30.Age1)>0)
OR ((DetailedData30.Age2)>0)
OR ((DetailedData30.Age3)>0)
OR (([DetailedData30].[Age4]+[DetailedData30].[Over])
0));

As I stated, if I break this into two separate queries they run fine, but I
get an error when UNION'ed. Any help would be appreciated. Also, if you
have any suggestions that would improve performance within the query I'd
appreciate it.

George



.
Click to expand...
Click to expand...
 
It sounds like JET does like (one of) the WHERE clauses
but I can't see what wrong. Try:

1. Repair & Compact the database.

2. Copy the SQL String to a new Query.

I found in a few cases when there are corruptions in the
database, Queries will behave very oddly.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top