format a number to remove dashes?

Posted on 2004-10-25
Last Modified: 2009-07-29
I have the following query:

SELECT [Litho Data].TTLotID
FROM [Litho Data];

The data looks like:

I need it to look like:   545217522

How do you do that?

Also  - it needs to be formatted to be a number and not a character string.
Question by:JoseDavila
    LVL 18

    Accepted Solution

    SELECT clng(Replace([Litho Data].[TTLotID],"-",""))
    FROM [Litho Data];

    Try that....Mike

    LVL 4

    Assisted Solution

    Check this out

    To convert it to an integer use CInt()

    LVL 16

    Assisted Solution

    Jay, CInt converts numeric string values to Integers, this is true, but only if they are short integers.  545217522 is too large to be an Integer value and thus CInt("545217522") will throw an overflow exception!  
    CLng converts numeric string values to Long Integer and is more appropriate for values of this range.  
    (PS: CDbl will convert such values into Double precision floating point values, if that is preferred.)
    LVL 4

    Expert Comment

    yeah, i caught that as me and data-man posted almost simealtaneously, but since h already had it i didn't correct myself

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    875 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

    8 Experts available now in Live!

    Get 1:1 Help Now