Data Mismatch in Excel 2010

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

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?
 
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?
Click to expand...

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
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
 
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
Click to expand...

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

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
Click to expand...

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
 
Back
Top