Solved

Data type mismatch in critia expression when sorting

Posted on 2004-10-26
383 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
Question by:JoseDavila
    9 Comments
     
    LVL 41

    Expert Comment

    by:shanesuebsahakarn
    Probably due to Null values. Try:

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

    Author Comment

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

    Expert Comment

    by:shanesuebsahakarn
    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
     

    Author Comment

    by:JoseDavila
    Correct.  
    0
     
    LVL 41

    Expert Comment

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

    Author Comment

    by:JoseDavila
    2002.
    0
     
    LVL 48

    Accepted Solution

    by:
    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
    Is [TTLotID] in the table [Litho Date] a text field?

    Patrick
    0
     
    LVL 44

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now