Results 1 to 2 of 2
  1. #1
    katrinka is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Location
    VACAVILLE, CA
    Posts
    1

    Data type mismatch in criteria expression when using query to create a join with a calculated field

    I am trying to create a query that joins a text field with a calculated field based on a text field but receive a "data type mismatch in criteria expression" error.

    The calculated field in table1 uses "replace" function to build expression from a text field; however it will not allow me to join to text field in table2.

    However, i am able to create a query and join same text field from table2 to the calculated field in table1. Does not make any sense that it allows me to join these fields from different tables only if i am "including all records from table2 and only matching from table1"; it does not work if i join fields to "include all records from table1 and only matching from table 2.

    can anyone help?


    here is sql view of query not working:

    SELECT [Query2_HOSPLOG W_CPT].[last name4], [Query2_HOSPLOG W_CPT].[First name], [Query2_HOSPLOG W_CPT].Date, [Query2_HOSPLOG W_CPT].[CPT CODE], [Query2_HOSPLOG W_CPT].QTY, [Query2_HOSPLOG W_CPT].[Patient DOB], [Query2_HOSPLOG W_CPT].Comments, [Query2_HOSPLOG W_CPT].[OLD CPT CODE]
    FROM [Query2_HOSPLOG W_CPT] RIGHT JOIN [IMX LOG] ON ([Query2_HOSPLOG W_CPT].[last name4] = [IMX LOG].[LAST NAME]) AND ([Query2_HOSPLOG W_CPT].Date = [IMX LOG].DOS) AND ([Query2_HOSPLOG W_CPT].[CPT CODE] = [IMX LOG].CPT);




    here is sql view of same query but joins are reversed that does work (NOTE ONLY DIFFERENCE IS RIGHT JOIN VS LEFT JOIN):

    SELECT [Query2_HOSPLOG W_CPT].[last name4], [Query2_HOSPLOG W_CPT].[First name], [Query2_HOSPLOG W_CPT].Date, [Query2_HOSPLOG W_CPT].[CPT CODE], [Query2_HOSPLOG W_CPT].QTY, [Query2_HOSPLOG W_CPT].[Patient DOB], [Query2_HOSPLOG W_CPT].Comments, [Query2_HOSPLOG W_CPT].[OLD CPT CODE]
    FROM [Query2_HOSPLOG W_CPT] LEFT JOIN [IMX LOG] ON ([Query2_HOSPLOG W_CPT].[last name4] = [IMX LOG].[LAST NAME]) AND ([Query2_HOSPLOG W_CPT].Date = [IMX LOG].DOS) AND ([Query2_HOSPLOG W_CPT].[CPT CODE] = [IMX LOG].CPT);

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I did not study all of your post but, calculated fields are not going to play well on a JOIN. The way I understand it is that a calculated field is also a multivalue field. SQL will not know how to evaluate a multivalue field in the FROM clause. I would retrieve the sum from the calc field in another query. In the other query, that is calculating the calc field, include a key field that you can join on. Then, use the new second query as a subquery and JOIN that to your original query. In other words, do the math first, and then JOIN the result.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch in criteria expression
    By khughes46 in forum Queries
    Replies: 12
    Last Post: 05-01-2014, 07:34 AM
  2. Replies: 2
    Last Post: 12-21-2013, 11:37 AM
  3. Replies: 4
    Last Post: 05-17-2013, 04:00 PM
  4. Data Type Mismatch in Criteria Expression
    By dehdahdoh in forum Queries
    Replies: 13
    Last Post: 05-01-2013, 11:41 AM
  5. Data type mismatch in criteria expression
    By buienxg in forum Access
    Replies: 2
    Last Post: 11-22-2011, 10:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums