Data Mismatch in Excel 2010

Discussion in 'Microsoft Access' started by stainless, Feb 25, 2012.

  1. stainless

    stainless Guest

    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?
     
    stainless, Feb 25, 2012
    #1
    1. Advertisements

  2. On Sat, 25 Feb 2012 05:26:01 -0800 (PST), stainless <>
    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/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Feb 25, 2012
    #2
    1. Advertisements

  3. stainless

    stainless Guest

    Re: Data Mismatch in Access 2010

    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
     
    stainless, Feb 25, 2012
    #3
  4. stainless

    stainless Guest

    Re: Data Mismatch in Access 2010

    On Feb 25, 4:52 pm, stainless <> 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
     
    stainless, Feb 25, 2012
    #4
  5. stainless

    Phil Hunt Guest

    Re: Data Mismatch in Access 2010

    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" <> wrote in message
    news:...
    On Feb 25, 4:52 pm, stainless <> 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
     
    Phil Hunt, Feb 25, 2012
    #5
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ted

    Data type mismatch

    Ted, Aug 7, 2003, in forum: Microsoft Access
    Replies:
    1
    Views:
    199
    Ken Snell
    Aug 8, 2003
  2. Winnie

    data type mismatch

    Winnie, Nov 10, 2003, in forum: Microsoft Access
    Replies:
    2
    Views:
    253
    Van T. Dinh
    Nov 10, 2003
  3. WhiZa

    Data type mismatch in criteria expression

    WhiZa, Jan 25, 2004, in forum: Microsoft Access
    Replies:
    2
    Views:
    231
    Allen Browne
    Jan 26, 2004
  4. Ant

    Data types mismatch when linking to Excel

    Ant, Aug 18, 2005, in forum: Microsoft Access
    Replies:
    6
    Views:
    151
    Larry Daugherty
    Aug 19, 2005
  5. amach

    Access 2010 very slow with Sharepoint 2010

    amach, May 27, 2010, in forum: Microsoft Access
    Replies:
    1
    Views:
    879
    David W. Fenton
    May 29, 2010
Loading...

Share This Page