PC Review


Reply
Thread Tools Rate Thread

Data Mismatch in Excel 2010

 
 
stainless
Guest
Posts: n/a
 
      25th Feb 2012
I have been writing a long query to join, by union, several other
table queries, thus combining similar data into one query.

For example, I have an AGE column in "Table 1 Query" (selects data
from Table 1 based on some specific criteria) and I am also have an
AGE and INFANT column in "Table 2 Query" (selects data from Table 2
based on specific criteria).



The AGE field and the INFANT field in both tables are dataytype TEXT:

i.e. Table 1 - AGE datatype TEXT
Table 2 - AGE datatype TEXT, INFANT datatype TEXT

There are no nulls in the columns.

The Union statement is attempting to Union:

[Table 1 Query].[AGE]

with

IIF([Table 2 Query].[AGE]>0,Cstr([Table 2 Query].[AGE]),IIF([Table 2
Query].[INFANT]<>"",Cstr([Table 2 Query].[INFANT]),Cstr([Table 2
Query].[AGE]))),

This is resuting in a "datatype mismatch in criteria expression".
However, if I run the table 2 query select statment on its own, it
works fine. So there is some issue in combining the columns from the 2
queries. But they look to me like they would both be text. Any ideas
why this would be treated as a mismatch?
 
Reply With Quote
 
 
 
 
John W. Vinson
Guest
Posts: n/a
 
      25th Feb 2012
On Sat, 25 Feb 2012 05:26:01 -0800 (PST), stainless <(E-Mail Removed)>
wrote:

>I have been writing a long query to join, by union, several other
>table queries, thus combining similar data into one query.
>
>For example, I have an AGE column in "Table 1 Query" (selects data
>from Table 1 based on some specific criteria) and I am also have an
>AGE and INFANT column in "Table 2 Query" (selects data from Table 2
>based on specific criteria).
>
>
>
>The AGE field and the INFANT field in both tables are dataytype TEXT:
>
>i.e. Table 1 - AGE datatype TEXT
>Table 2 - AGE datatype TEXT, INFANT datatype TEXT
>
>There are no nulls in the columns.
>
>The Union statement is attempting to Union:
>
>[Table 1 Query].[AGE]
>
>with
>
>IIF([Table 2 Query].[AGE]>0,Cstr([Table 2 Query].[AGE]),IIF([Table 2
>Query].[INFANT]<>"",Cstr([Table 2 Query].[INFANT]),Cstr([Table 2
>Query].[AGE]))),
>
>This is resuting in a "datatype mismatch in criteria expression".
>However, if I run the table 2 query select statment on its own, it
>works fine. So there is some issue in combining the columns from the 2
>queries. But they look to me like they would both be text. Any ideas
>why this would be treated as a mismatch?


Might it be the field size? UNION fields must match in both size and datatype.
You might need to pad one of the AGE fields with blanks to get it to match.

In your subject you say Excel but this is evidently all in Access, right? And
if the fields are already Text, what's the point of the CStr function calls?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com
 
Reply With Quote
 
 
 
 
stainless
Guest
Posts: n/a
 
      25th Feb 2012
Apologies did mean Access 2010 (working on Excel for something else so
it was on my mind).

I will give the padding a go and reply if it works.

Cheers
 
Reply With Quote
 
stainless
Guest
Posts: n/a
 
      25th Feb 2012
On Feb 25, 4:52*pm, stainless <(E-Mail Removed)> wrote:
> Apologies did mean Access 2010 (working on Excel for something else so
> it was on my mind).
>
> I will give the padding a go and reply if it works.
>
> Cheers


Unfortunately, I have realised I am not sure how to do this.

The original sizes for all the fields on both original tables (and
thus the Queries used in this select) are a standard 255 text
characters
 
Reply With Quote
 
Phil Hunt
Guest
Posts: n/a
 
      25th Feb 2012
take out the CSTR.
Age in table1 is probablly number. And the second query put out a string.
BTW, size does not matter that much

"stainless" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Feb 25, 4:52 pm, stainless <(E-Mail Removed)> wrote:
> Apologies did mean Access 2010 (working on Excel for something else so
> it was on my mind).
>
> I will give the padding a go and reply if it works.
>
> Cheers


Unfortunately, I have realised I am not sure how to do this.

The original sizes for all the fields on both original tables (and
thus the Queries used in this select) are a standard 255 text
characters


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Excel 2010 table into Access 2010 - not listed as range rob11marmion@gmail.com Microsoft Access External Data 1 17th Feb 2013 11:04 PM
Excel 2010 Table not showing in Access 2010 Import List rob11marmion@gmail.com Microsoft Excel Discussion 0 21st Dec 2012 06:22 AM
Can't open workbook in excel 64 bit 2010 - but can in excel 2010 3 Darrell Microsoft Excel Programming 5 12th Jan 2010 10:03 PM
Office 2010 & Outlook 2010 and Business Contact Manager AvrahamC Microsoft Outlook BCM 0 4th Jan 2010 02:56 PM
Type mismatch error (different than previous type mismatch?) Roberta Microsoft Access VBA Modules 3 9th Jan 2004 07:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:04 AM.