[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Data type mismatch in critia expression when sorting

Posted on 2004-10-26
9
Medium Priority
?
399 Views
Last Modified: 2012-06-27
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];
0
Comment
Question by:JoseDavila
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12413371
Probably due to Null values. Try:

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

Author Comment

by:JoseDavila
ID: 12413422
NO.  It still has the same error.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12413440
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JoseDavila
ID: 12413452
Correct.  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12413472
Hmm. What version of Access and what service pack?
0
 

Author Comment

by:JoseDavila
ID: 12413608
2002.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 12413745
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
 
LVL 1

Expert Comment

by:plauwaer
ID: 12414993
Is [TTLotID] in the table [Litho Date] a text field?

Patrick
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12415896
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question