Error with UNION query

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
 
V

Van T. Dinh

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

George Hutto

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

George Hutto

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

Van T. Dinh

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

union query not displaying? 5
Error 2448 on Form with DLookup 0
IIF statement in Union query 0
Union to populate cbo2 based on cbo1 7
HELP WITH "WHERE" clause 2
Report Help. If the query has no data 1
Need help with Code Please!!! 26
Need Help | Rolling out new App. | Trouble with some computers. 10

Top