Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Removing Decimals and Commas from a value

Discussion in 'Information Technology' started by Send Object Command - Two attachments, Nov 11, 2005.

  1. How can I remove comma's and decimals from a certain value using a formula in
    a query?
    Example: 2,352.00
    Result: 235200
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Try this

    In SQL
    Select Round([FieldName],2)*100 As Newumber From TableName

    In the query it will look like
    NewNumber: Round([FieldName],2)*100

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Send Object Command - Two attachments" wrote:

    > How can I remove comma's and decimals from a certain value using a formula in
    > a query?
    > Example: 2,352.00
    > Result: 235200
     
  4. John Welch

    John Welch
    Expand Collapse
    Guest

    You can do it in two steps: first remove commas, then dots, using the
    replace function. Here's an example that will work:

    SELECT Table1.original, Replace([original],",","") AS no_commas,
    Replace([no_commas],".","") AS no_dots
    FROM Table1;

    There might be a way to do it in one step, but I can't think of it right
    off.
    HTH

    "Send Object Command - Two attachments"
    <SendObjectCommandTwoattachments@discussions.microsoft.com> wrote in message
    news:8E9F502B-2364-45CE-9AC0-75EEA0110581@microsoft.com...
    > How can I remove comma's and decimals from a certain value using a formula
    > in
    > a query?
    > Example: 2,352.00
    > Result: 235200
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Send Object Command - Two attachments wrote:
    > How can I remove comma's and decimals from a certain value using a
    > formula in a query?
    > Example: 2,352.00
    > Result: 235200


    First question. Is the subject a number or is it really text?

    If it is a number, then it sounds like a formatting issue.

    --
    Joseph Meehan

    Dia duit
     
  6. This is what I got to work late last night:
    Val(Replace(Replace([FieldName],".",""),",","."))

    Thanks to all

    "Joseph Meehan" wrote:

    > Send Object Command - Two attachments wrote:
    > > How can I remove comma's and decimals from a certain value using a
    > > formula in a query?
    > > Example: 2,352.00
    > > Result: 235200

    >
    > First question. Is the subject a number or is it really text?
    >
    > If it is a number, then it sounds like a formatting issue.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     

Share This Page