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