We help IT Professionals succeed at work.

Type mismatch in expression

SteveL13
SteveL13 asked
on
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));

--Steve
Comment
Watch Question

Analyst Assistant
CERTIFIED EXPERT
Commented:
Try Val:

Val(Left([LccN),3) AS WC

Author

Commented:
That seems to be missing a parentheses or something.
CERTIFIED EXPERT
Top Expert 2016
Commented:
thatt will give you an error, try this

Val(Left([LccN],3)) AS WC

or

clng(Left([LccN],3)) AS WC
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Are you sure?

CLng will error if Left([Lccn],3) returns text, Val won't.

Both will return an error if LccN is Null, but that's because Left will return an error.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Oops, my bad.

Didn't realise I was missing a parenthesis.

Apologies to everyone.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.