UNION query and Data type mismatch in criteria expression

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.
0
Utf
1/23/2008 10:16:00 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
1914 Views

Similar Articles

[PageSpeed] 48

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


"rolaaus" wrote:

> 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.
0
Utf
1/23/2008 11:24:01 PM
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" wrote:

> Post your union query SQL.
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "rolaaus" wrote:
> 
> > 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.
0
Utf
1/23/2008 11:39:04 PM
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" wrote:

> 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" wrote:
> 
> > Post your union query SQL.
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "rolaaus" wrote:
> > 
> > > 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.
0
Utf
1/24/2008 12:22:00 AM
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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > Post your union query SQL.
> > > -- 
> > > KARL DEWEY
> > > Build a little - Test a little
> > > 
> > > 
> > > "rolaaus" wrote:
> > > 
> > > > 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.
0
Utf
1/24/2008 12:34:03 AM
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" <rolaaus@discussions.microsoft.com> wrote in message 
news:E46B40F6-8C5E-4DDF-B42F-81A00ADA7499@microsoft.com...
> Okey dokey,
>>
>> > 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. 


0
John
1/24/2008 1:06:54 PM
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" wrote:

> 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" wrote:
> 
> > 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], 

0
Utf
1/24/2008 5:27:08 PM
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" <rolaaus@discussions.microsoft.com> wrote in message 
news:96532223-85CD-48F3-AB10-C4D5C7C5C83E@microsoft.com...
> 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" wrote:
>
>> 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" wrote:
>>
>> > 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],
> 


0
John
1/24/2008 5:40:57 PM
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" wrote:

> 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" <rolaaus@discussions.microsoft.com> wrote in message 
> news:E46B40F6-8C5E-4DDF-B42F-81A00ADA7499@microsoft.com...
> > Okey dokey,
> >>
> >> > 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. 
> 
> 
> 
0
Utf
1/24/2008 6:49:03 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

Can't configure Outlook Express
I had Outlook express set up for the Money newsgroup, but somehow I lost it. Now I even forgot how to set it up again. I always get the error message saying "server cannot be found". The server name I typed in was microsoft.public.money, and I am not sure what should I type in here Thanks for help In microsoft.public.money, wj wrote: >I had Outlook express set up for the Money newsgroup, but >somehow I lost it. Now I even forgot how to set it up >again. I always get the error message saying "server >cannot be found". The server name I typed in was &g...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...

Calculating Subsets of Data
I have data for records that can span between 1 to 16 months.. I want to be able to calculate the average activity for the most recent six months in a query and be able to display that in a report. I can create the calculation in the query for the entire duration of months for each record but am stuck on how to have it choose only the most recent six months of data to do the calculation and understand that the six months may really only be between 1 to 6 months of data.. Any suggestions would be greatly appreciated..My skill level is somewhere between meatball surgeon and ki...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...

Group Data
Hi All, I have the following data Office January 2005 February 2005 ..... sales Collection Sales Collection A 1000 500 2000 2500 B 2000 1500 2500 1500 etc How can I get a bar chart based on Month Sales/Collection ? When I take all data grouped together (sales and collection) without grouping them!!??? thanks dO YOU WANT SOMETHING LIKE THIS? http://support.microsoft.com/default.aspx?scid=kb;en-us;218153 (sorry about the caps) <Melepoil@gmail.com> wrote ...

Query involving Strings : How To Return Matching Data From Both Ta
Good afternoon, I have tried my best on this but am unable to figure it out. --------------------------------------------------------------------- Scenario: Table A contains only one column titled [District]. Let's assume that there are three rows here: 1) Bay Area Rapid Transit 2) San Diego Zoo 3) San Mateo Table B contains other data with these fields: [GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees]. Let's assume that there are three rows here: 1) West Coast | CA | Bay Area Rapid Transit , 154, 205 2) CA | Southern | Bay Area Rapid Transit , 105, 206 3) Southw...

Wake up, 97 Query in 2000/03
I've converted a 97 database to 2000 (seems to do the same in 2003) and one of the select queries gives me no records when there should be quite a few. I tried copying the SQL to a new query and, lo and behold, it works fine. It feels like the SQL isn't compiling properly. Is there a better solution as I've got loads of these queries, hardwired into a number of reports and it's going to take a while to go through them all. The only thing to say about this particular query is that it was written some time ago and the designer was obviously happier with writing by hand ...