Data type mismatch in critia expression when sorting

I am trying to sort the TTLotID field, but I get the data type mismatch error.  Why is that and how can I fix it?


SELECT CLng(Replace([Litho Data].[TTLotID],"-","")) AS LotID
FROM [Litho Data];
JoseDavilaAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Try this:

SELECT Clng("0" & Replace([Litho Data].[TTLotID],"-","")) AS LotID
FROM [Litho Data];

or better:

SELECT VAL("0" & Replace([Litho Data].[TTLotID],"-","")) AS LotID
FROM [Litho Data];

/gustav
0
 
shanesuebsahakarnCommented:
Probably due to Null values. Try:

SELECT CLng(Replace(Nz([Litho Data].[TTLotID],"0"),"-","")) AS LotID
FROM [Litho Data];
0
 
JoseDavilaAuthor Commented:
NO.  It still has the same error.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
shanesuebsahakarnCommented:
You say you were trying to sort by the TTLotID field - I assume you mean you were trying to sort by the new LotID field?
0
 
JoseDavilaAuthor Commented:
Correct.  
0
 
shanesuebsahakarnCommented:
Hmm. What version of Access and what service pack?
0
 
JoseDavilaAuthor Commented:
2002.
0
 
plauwaerCommented:
Is [TTLotID] in the table [Litho Date] a text field?

Patrick
0
 
GRayLCommented:
I presume the value of TTLotID after the replace is less than 2,147,483,647.  That is the largest positive integer CLng will support.
0
All Courses

From novice to tech pro — start learning today.