UNION query and Data type mismatch in criteria expression

R

rolaaus

I did some searches on this problem before trying to post here to the
newsgroup, but what little information I found did not seem to help.

Here is the problem. I have a UNION query that joins 2 queries into 1
dataset. Each sub-query works fine individually, but when I place them into
a UNION query it gives the error message "Data Type mismatch in criteria
expression"

I first double-checked to make sure any JOIN fields were of the same data
type - which they are (everything is in TEXT format). Also, the information I
found suggested there might be problems when an empty field is used in one of
the JOIN fields, so I eliminated those for troubleshooting purposes, and I
still get the problem.

the last thing I tried was changing all types of JOINS (inner vs. outer). I
first made all of the joins only show fields that matched, then I made them
show any record that matched the main table I need, and only those in the
JOIN that matched - and I did this for both sub-queries, but still no joy.

Any suggestions would be greatly appreciated - especially since I don't seem
to have this problem before I converted every field in every table to TEXT -
you see, I am importing some data from Excel, and you know how Access will
"guess" what the field type should be based on the first several rows of data
in Excel, well, I wanted everything to be imported without any probelms, so I
forced Access (through VBA + automation) to import the Excel file as strictly
text. Before I did this, my join query worked just fine (go figure). But
now, every field in every table is TEXT size 255.
 
R

rolaaus

Okey dokey,

SELECT [tbl_Transactions].[Request From Date], [tbl_Transactions].[Request
To Date], [tbl_Transactions].[Account ID], [tbl_Transactions].[Account Name],
[tbl_Transactions].[Entry Date], [tbl_Transactions].[Trade Date],
[tbl_Transactions].[Settlement Date], [tbl_Transactions].[CUSIP Id],
[tbl_Transactions].[Ticker Symbol], [tbl_Transactions].SEDOL,
[tbl_Transactions].[Port P/I], [tbl_Transactions].[Transaction Type Cd],
[tbl_Transactions].Explanation, Abs([Units]) AS Unit,
[tbl_Transactions].Price, [tbl_Transactions].Commissions,
[tbl_Transactions].[SEC Fees], [tbl_Transactions].[Miscellaneous Fees],
IIf([Transaction Type Cd]=300,[Net Cash Amt],Abs([Net Cash Amt])) AS [Net
Cash Amnt], [tbl_Transactions].[Federal Tax Cost Amt],
[tbl_Transactions].[Short Term Gain/Loss Amt], [tbl_Transactions].[Long Term
Gain/Loss Amt], tbl_lu_Assett_Class_Code.[Asset Class Cd]
FROM [tbl_Transactions] LEFT JOIN tbl_lu_Assett_Class_Code ON
[tbl_Transactions].[CUSIP Id] = tbl_lu_Assett_Class_Code.[CUSIP Id]
UNION ALL SELECT #12/1/2007# AS [Reguest From Date], #12/31/2007# AS
[Request To Date], IIf(Left([tbl_Pending].[Account ID],4)="7754","000" &
[tbl_Pending].[Account ID],[tbl_Pending].[Account ID]) AS AccountID, "" AS
[Account Name], "" AS [Entry Date], [tbl_Pending].[Trade Date], "" AS
[Settlement Date], [tbl_Pending].[CUSIP Id], "" AS [Ticker Symbol], "" AS
SEDOL, "" AS [Port P/I], [tbl_Pending].[Trade Type Cd] AS [Transaction Type
Cd], "" AS Explanation, Abs([Execution Shares/Par]) AS Unit,
[tbl_Pending].[Current Price] AS Price, "" AS Commissions, "" AS [SEC Fees],
"" AS [Miscellaneous Fees], Abs([tbl_Pending].[Principal Cash Amt]) AS [Net
Cash Amt], "" AS [Federal Tax Cost Amt], "" AS [Short Term Gain/Loss Amt], ""
AS [Long Term Gain/Loss Amt], [tbl_Holdings].[Asset Class Cd]
FROM [tbl_Pending] INNER JOIN [tbl_Holdings] ON ([tbl_Pending].[Account ID]
= [tbl_Holdings].[Account ID]);
 
K

KARL DEWEY

I see what looks like a typo --- Abs([Net Cash Amt])) AS [Net Cash Amnt],
should be --- Abs([Net Cash Amt])) AS [Net Cash Amt],


--
KARL DEWEY
Build a little - Test a little


rolaaus said:
Okey dokey,

SELECT [tbl_Transactions].[Request From Date], [tbl_Transactions].[Request
To Date], [tbl_Transactions].[Account ID], [tbl_Transactions].[Account Name],
[tbl_Transactions].[Entry Date], [tbl_Transactions].[Trade Date],
[tbl_Transactions].[Settlement Date], [tbl_Transactions].[CUSIP Id],
[tbl_Transactions].[Ticker Symbol], [tbl_Transactions].SEDOL,
[tbl_Transactions].[Port P/I], [tbl_Transactions].[Transaction Type Cd],
[tbl_Transactions].Explanation, Abs([Units]) AS Unit,
[tbl_Transactions].Price, [tbl_Transactions].Commissions,
[tbl_Transactions].[SEC Fees], [tbl_Transactions].[Miscellaneous Fees],
IIf([Transaction Type Cd]=300,[Net Cash Amt],Abs([Net Cash Amt])) AS [Net
Cash Amnt], [tbl_Transactions].[Federal Tax Cost Amt],
[tbl_Transactions].[Short Term Gain/Loss Amt], [tbl_Transactions].[Long Term
Gain/Loss Amt], tbl_lu_Assett_Class_Code.[Asset Class Cd]
FROM [tbl_Transactions] LEFT JOIN tbl_lu_Assett_Class_Code ON
[tbl_Transactions].[CUSIP Id] = tbl_lu_Assett_Class_Code.[CUSIP Id]
UNION ALL SELECT #12/1/2007# AS [Reguest From Date], #12/31/2007# AS
[Request To Date], IIf(Left([tbl_Pending].[Account ID],4)="7754","000" &
[tbl_Pending].[Account ID],[tbl_Pending].[Account ID]) AS AccountID, "" AS
[Account Name], "" AS [Entry Date], [tbl_Pending].[Trade Date], "" AS
[Settlement Date], [tbl_Pending].[CUSIP Id], "" AS [Ticker Symbol], "" AS
SEDOL, "" AS [Port P/I], [tbl_Pending].[Trade Type Cd] AS [Transaction Type
Cd], "" AS Explanation, Abs([Execution Shares/Par]) AS Unit,
[tbl_Pending].[Current Price] AS Price, "" AS Commissions, "" AS [SEC Fees],
"" AS [Miscellaneous Fees], Abs([tbl_Pending].[Principal Cash Amt]) AS [Net
Cash Amt], "" AS [Federal Tax Cost Amt], "" AS [Short Term Gain/Loss Amt], ""
AS [Long Term Gain/Loss Amt], [tbl_Holdings].[Asset Class Cd]
FROM [tbl_Pending] INNER JOIN [tbl_Holdings] ON ([tbl_Pending].[Account ID]
= [tbl_Holdings].[Account ID]);


KARL DEWEY said:
Post your union query SQL.
Click to expand...
Click to expand...
 
R

rolaaus

The reason I did that is because, when i origionally created the query, it
wouldn't allow me to alias the field name with the field name itself, so I
added an N to Amt to make Amnt - just a work-around for circular referencing
problems (if I am remembering correctly). This shouldn't be causing any
problems, in my opinion?

KARL DEWEY said:
I see what looks like a typo --- Abs([Net Cash Amt])) AS [Net Cash Amnt],
should be --- Abs([Net Cash Amt])) AS [Net Cash Amt],


--
KARL DEWEY
Build a little - Test a little


rolaaus said:
Okey dokey,

SELECT [tbl_Transactions].[Request From Date], [tbl_Transactions].[Request
To Date], [tbl_Transactions].[Account ID], [tbl_Transactions].[Account Name],
[tbl_Transactions].[Entry Date], [tbl_Transactions].[Trade Date],
[tbl_Transactions].[Settlement Date], [tbl_Transactions].[CUSIP Id],
[tbl_Transactions].[Ticker Symbol], [tbl_Transactions].SEDOL,
[tbl_Transactions].[Port P/I], [tbl_Transactions].[Transaction Type Cd],
[tbl_Transactions].Explanation, Abs([Units]) AS Unit,
[tbl_Transactions].Price, [tbl_Transactions].Commissions,
[tbl_Transactions].[SEC Fees], [tbl_Transactions].[Miscellaneous Fees],
IIf([Transaction Type Cd]=300,[Net Cash Amt],Abs([Net Cash Amt])) AS [Net
Cash Amnt], [tbl_Transactions].[Federal Tax Cost Amt],
[tbl_Transactions].[Short Term Gain/Loss Amt], [tbl_Transactions].[Long Term
Gain/Loss Amt], tbl_lu_Assett_Class_Code.[Asset Class Cd]
FROM [tbl_Transactions] LEFT JOIN tbl_lu_Assett_Class_Code ON
[tbl_Transactions].[CUSIP Id] = tbl_lu_Assett_Class_Code.[CUSIP Id]
UNION ALL SELECT #12/1/2007# AS [Reguest From Date], #12/31/2007# AS
[Request To Date], IIf(Left([tbl_Pending].[Account ID],4)="7754","000" &
[tbl_Pending].[Account ID],[tbl_Pending].[Account ID]) AS AccountID, "" AS
[Account Name], "" AS [Entry Date], [tbl_Pending].[Trade Date], "" AS
[Settlement Date], [tbl_Pending].[CUSIP Id], "" AS [Ticker Symbol], "" AS
SEDOL, "" AS [Port P/I], [tbl_Pending].[Trade Type Cd] AS [Transaction Type
Cd], "" AS Explanation, Abs([Execution Shares/Par]) AS Unit,
[tbl_Pending].[Current Price] AS Price, "" AS Commissions, "" AS [SEC Fees],
"" AS [Miscellaneous Fees], Abs([tbl_Pending].[Principal Cash Amt]) AS [Net
Cash Amt], "" AS [Federal Tax Cost Amt], "" AS [Short Term Gain/Loss Amt], ""
AS [Long Term Gain/Loss Amt], [tbl_Holdings].[Asset Class Cd]
FROM [tbl_Pending] INNER JOIN [tbl_Holdings] ON ([tbl_Pending].[Account ID]
= [tbl_Holdings].[Account ID]);


KARL DEWEY said:
Post your union query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I did some searches on this problem before trying to post here to the
newsgroup, but what little information I found did not seem to help.

Here is the problem. I have a UNION query that joins 2 queries into 1
dataset. Each sub-query works fine individually, but when I place them into
a UNION query it gives the error message "Data Type mismatch in criteria
expression"

I first double-checked to make sure any JOIN fields were of the same data
type - which they are (everything is in TEXT format). Also, the information I
found suggested there might be problems when an empty field is used in one of
the JOIN fields, so I eliminated those for troubleshooting purposes, and I
still get the problem.

the last thing I tried was changing all types of JOINS (inner vs. outer). I
first made all of the joins only show fields that matched, then I made them
show any record that matched the main table I need, and only those in the
JOIN that matched - and I did this for both sub-queries, but still no joy.

Any suggestions would be greatly appreciated - especially since I don't seem
to have this problem before I converted every field in every table to TEXT -
you see, I am importing some data from Excel, and you know how Access will
"guess" what the field type should be based on the first several rows of data
in Excel, well, I wanted everything to be imported without any probelms, so I
forced Access (through VBA + automation) to import the Excel file as strictly
text. Before I did this, my join query worked just fine (go figure). But
now, every field in every table is TEXT size 255.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You might have a mismatch in the data type between the queries making up the
union. You said that all the fields are TEXT fields - yet the first two
"Fields" after the UNION ALL are entered as DATES not Strings.

And other fields are treated as if they are numbers. Abs(Units) is treated
as if it were a number - actually using Abs will recast it as a number.
, IIf([Transaction Type Cd]=300,[Net Cash Amt], Abs([Net Cash Amt])) AS [Net
Cash Amnt] - Should that be
, IIf([Transaction Type Cd]="300",[Net Cash Amt], Abs([Net Cash Amt])) AS
[Net Cash Amnt]

You will get an error (Type Mismatch) if Net Cash Amt is a zero-length
string. Also the same problem with Abs(Units) or any other place you use
ABS

SELECT [tbl_Transactions].[Request From Date]
, [tbl_Transactions].[Request To Date]
, [tbl_Transactions].[Account ID]
, [tbl_Transactions].[Account Name]
, [tbl_Transactions].[Entry Date]
, [tbl_Transactions].[Trade Date]
, [tbl_Transactions].[Settlement Date]
, [tbl_Transactions].[CUSIP Id]
, [tbl_Transactions].[Ticker Symbol]
, [tbl_Transactions].SEDOL
, [tbl_Transactions].[Port P/I]
, [tbl_Transactions].[Transaction Type Cd]
, [tbl_Transactions].Explanation
, Abs([Units]) AS Unit
, [tbl_Transactions].Price
, [tbl_Transactions].Commissions
, [tbl_Transactions].[SEC Fees]
, [tbl_Transactions].[Miscellaneous Fees]
, IIf([Transaction Type Cd]=300,[Net Cash Amt], Abs([Net Cash Amt])) AS [Net
Cash Amnt]
, [tbl_Transactions].[Federal Tax Cost Amt]
, [tbl_Transactions].[Short Term Gain/Loss Amt]
, [tbl_Transactions].[Long Term Gain/Loss Amt]
, tbl_lu_Assett_Class_Code.[Asset Class Cd]
FROM [tbl_Transactions] LEFT JOIN tbl_lu_Assett_Class_Code ON
[tbl_Transactions].[CUSIP Id] = tbl_lu_Assett_Class_Code.[CUSIP Id]
UNION ALL
SELECT #12/1/2007# AS [Reguest From Date]
, #12/31/2007# AS [Request To Date]
, IIf(Left([tbl_Pending].[Account ID],4)="7754","000" &
[tbl_Pending].[Account ID],[tbl_Pending].[Account ID]) AS AccountID
, "" AS [Account Name]
, "" AS [Entry Date]
, [tbl_Pending].[Trade Date]
, "" AS [Settlement Date]
, [tbl_Pending].[CUSIP Id]
, "" AS [Ticker Symbol]
, "" AS SEDOL
, "" AS [Port P/I]
, [tbl_Pending].[Trade Type Cd] AS [Transaction Type Cd]
, "" AS Explanation
, Abs([Execution Shares/Par]) AS Unit
, [tbl_Pending].[Current Price] AS Price
, "" AS Commissions
, "" AS [SEC Fees]
, "" AS [Miscellaneous Fees]
, Abs([tbl_Pending].[Principal Cash Amt]) AS [Net Cash Amt]
, "" AS [Federal Tax Cost Amt]
, "" AS [Short Term Gain/Loss Amt]
, "" AS [Long Term Gain/Loss Amt]
, [tbl_Holdings].[Asset Class Cd]
FROM [tbl_Pending] INNER JOIN [tbl_Holdings]
ON ([tbl_Pending].[Account ID] = [tbl_Holdings].[Account ID]);


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
R

rolaaus

John,

Thanks for the input. It makes since, trying to run a math expression on a
string, and getting a data type conversion error, but it begs the question -
why is this only happening on a UNION query and not when I run each seperate
query individually?

rolaaus said:
The reason I did that is because, when i origionally created the query, it
wouldn't allow me to alias the field name with the field name itself, so I
added an N to Amt to make Amnt - just a work-around for circular referencing
problems (if I am remembering correctly). This shouldn't be causing any
problems, in my opinion?

KARL DEWEY said:
I see what looks like a typo --- Abs([Net Cash Amt])) AS [Net Cash Amnt],
should be --- Abs([Net Cash Amt])) AS [Net Cash Amt],
Click to expand...
Click to expand...
 
J

John Spencer

I don't know. Perhaps the UNION query is more sensitive to what is going
on.

You can try troubleshooting, by removing sets of fields until you see which
field or fields are the culprits.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

rolaaus said:
John,

Thanks for the input. It makes since, trying to run a math expression on
a
string, and getting a data type conversion error, but it begs the
question -
why is this only happening on a UNION query and not when I run each
seperate
query individually?

rolaaus said:
The reason I did that is because, when i origionally created the query,
it
wouldn't allow me to alias the field name with the field name itself, so
I
added an N to Amt to make Amnt - just a work-around for circular
referencing
problems (if I am remembering correctly). This shouldn't be causing any
problems, in my opinion?

KARL DEWEY said:
I see what looks like a typo --- Abs([Net Cash Amt])) AS [Net Cash
Amnt],
should be --- Abs([Net Cash Amt])) AS [Net Cash Amt],
Click to expand...
Click to expand...
Click to expand...
 
R

rolaaus

I also forgot to mention that these field names (ie. the ones stating "Date")
in them, the names themselves come in from Excel, so that is why they have
the word "date" in them.

Actually, I think part of the problem might be the Absolute expression with
Null values, as well as I was trying to add a Zero length string "" into some
fields (some of them being the field that I was auto-populating with a Date
and Access treated as a date becuase of the #. Even switching those to "
instead o f #, I keep getting the error, but I think that may have to do with
the fact that I have abonded the Union query and don't want to mess with
changing the Absolute expression.

What I'm ending up doing is making a 3 tablle to combine the 2 dataset's
into then expporting from that. Probably an extra unneccessary step, once I
find out what is wrong, but this process is helping me figure out all the
problems I'm having - in fact, I'm past this Data Type conversion problem and
now have other fish to fry (receiving duplicate records somehow in my final
"combined" export table - even after tracking down one possible solution and
implementing that).

John Spencer said:
You might have a mismatch in the data type between the queries making up the
union. You said that all the fields are TEXT fields - yet the first two
"Fields" after the UNION ALL are entered as DATES not Strings.

And other fields are treated as if they are numbers. Abs(Units) is treated
as if it were a number - actually using Abs will recast it as a number.
, IIf([Transaction Type Cd]=300,[Net Cash Amt], Abs([Net Cash Amt])) AS [Net
Cash Amnt] - Should that be
, IIf([Transaction Type Cd]="300",[Net Cash Amt], Abs([Net Cash Amt])) AS
[Net Cash Amnt]

You will get an error (Type Mismatch) if Net Cash Amt is a zero-length
string. Also the same problem with Abs(Units) or any other place you use
ABS

SELECT [tbl_Transactions].[Request From Date]
, [tbl_Transactions].[Request To Date]
, [tbl_Transactions].[Account ID]
, [tbl_Transactions].[Account Name]
, [tbl_Transactions].[Entry Date]
, [tbl_Transactions].[Trade Date]
, [tbl_Transactions].[Settlement Date]
, [tbl_Transactions].[CUSIP Id]
, [tbl_Transactions].[Ticker Symbol]
, [tbl_Transactions].SEDOL
, [tbl_Transactions].[Port P/I]
, [tbl_Transactions].[Transaction Type Cd]
, [tbl_Transactions].Explanation
, Abs([Units]) AS Unit
, [tbl_Transactions].Price
, [tbl_Transactions].Commissions
, [tbl_Transactions].[SEC Fees]
, [tbl_Transactions].[Miscellaneous Fees]
, IIf([Transaction Type Cd]=300,[Net Cash Amt], Abs([Net Cash Amt])) AS [Net
Cash Amnt]
, [tbl_Transactions].[Federal Tax Cost Amt]
, [tbl_Transactions].[Short Term Gain/Loss Amt]
, [tbl_Transactions].[Long Term Gain/Loss Amt]
, tbl_lu_Assett_Class_Code.[Asset Class Cd]
FROM [tbl_Transactions] LEFT JOIN tbl_lu_Assett_Class_Code ON
[tbl_Transactions].[CUSIP Id] = tbl_lu_Assett_Class_Code.[CUSIP Id]
UNION ALL
SELECT #12/1/2007# AS [Reguest From Date]
, #12/31/2007# AS [Request To Date]
, IIf(Left([tbl_Pending].[Account ID],4)="7754","000" &
[tbl_Pending].[Account ID],[tbl_Pending].[Account ID]) AS AccountID
, "" AS [Account Name]
, "" AS [Entry Date]
, [tbl_Pending].[Trade Date]
, "" AS [Settlement Date]
, [tbl_Pending].[CUSIP Id]
, "" AS [Ticker Symbol]
, "" AS SEDOL
, "" AS [Port P/I]
, [tbl_Pending].[Trade Type Cd] AS [Transaction Type Cd]
, "" AS Explanation
, Abs([Execution Shares/Par]) AS Unit
, [tbl_Pending].[Current Price] AS Price
, "" AS Commissions
, "" AS [SEC Fees]
, "" AS [Miscellaneous Fees]
, Abs([tbl_Pending].[Principal Cash Amt]) AS [Net Cash Amt]
, "" AS [Federal Tax Cost Amt]
, "" AS [Short Term Gain/Loss Amt]
, "" AS [Long Term Gain/Loss Amt]
, [tbl_Holdings].[Asset Class Cd]
FROM [tbl_Pending] INNER JOIN [tbl_Holdings]
ON ([tbl_Pending].[Account ID] = [tbl_Holdings].[Account ID]);


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

rolaaus said:
Okey dokey,
Click to expand...
Click to expand...
 

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

Data type mismatch in criteria expression 7
Error: Data type mismatch in criteria expression 0
Number to Number "Data Type Mismatch in Expression" in Join 1
'Data type mismatch in criteria expression' error in Count query 2
'Data type mismatch in criteria expression' error in Count query 1
Data type mismatch in criteria expression 4
Join on a UNION query 2
data mismatch 2

Top