I am trying to create a query, (query 1), which joins another query, (query 2) to a table. Query 2 is joined to the table using a field named “WC” in Query 2 to a field in the table named “LccN”. But when I try to run the query I get an error, “Type mismatch in expression”.
The field “LccN” in the table is a number field. The field in query 2 comes from this SQL syntax and I’m wondering if it doesn’t have to somehow be formatted as a number. It is the field I am getting from “Left([LccN],3) AS WC” in the code below.
How can I convert this, "Left([LccN],3) AS WC", to a number field?
SELECT OpenJob.JobN, Customer.CustomerName, JobLabor.UpdateDate, JobLabor.LccN, JobLabor.Hours, JobLabor.LaborQuantity, Left([LccN],3) AS WC
FROM JobLabor RIGHT JOIN (Customer RIGHT JOIN OpenJob ON Customer.CustomerN = OpenJob.CustomerN) ON JobLabor.JobN = OpenJob.JobN
WHERE (((JobLabor.UpdateDate) Between #11/1/2009# And #10/31/2010#) AND ((JobLabor.LccN)>2));