Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Format a Number Without Decimal

    I have the following calculation I need to format


    GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])

    the total comes out to be 11368.71

    I need to remove the decimal and format it to look like this

    1136871

    When I try the format function, it rounds the number to 11369

    How can I do this?

    Thanks - AB

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try this:

    Code:
    GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
    GrossWages_Q4: (Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4)))
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Works - but 1 More Issue..

    Quote Originally Posted by CyberLynx
    Try this:

    Code:
    GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
    GrossWages_Q4: (Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4)))
    .

    The above works well - except the data has some fields without a decimal - the ones with even money


    example if the value is 11368.71 - it formats to 1136871 - fine!
    but if the value is 1359 - then I get an error

    some of the data has decimals, and some does not - this is a weird import, as it imports data with a decimal if the last 2 values are > .00 otherwise it just imports the value...


    any ideas? you got me further ahead than I was...

    Thanks! - AB

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    pic attached

    Here is a pic of the data - the testQ4 is the field with the code above...works, but not for all records

    AB
    Attached Thumbnails Attached Thumbnails testQ4.jpg  
    Last edited by abinboston; 11-11-05 at 00:52. Reason: wrong pic

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Try this:

    Code:
    GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
    GrossWages_Q4: (IIF(Instr(GrossWages_Q4, ".") > 0, Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4))), GrossWages_Q4)
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Aug 2004
    Posts
    173

    Almost.....

    Quote Originally Posted by CyberLynx
    Try this:

    Code:
    GrossWages_Q4: ([GROSWAGS_10]+[GROSWAGS_11]+[GROSWAGS_12])
    GrossWages_Q4: (IIF(Instr(GrossWages_Q4, ".") > 0, Left(GrossWages_Q4, InStr(GrossWages_Q4, ".") - 1) & Mid(GrossWages_Q4, InStr(GrossWages_Q4, ".") + 1, Len(GrossWages_Q4))), GrossWages_Q4)
    .
    Thanks for all your help - this is almost working.
    The pic attached shows what is happenning.

    in the data, some values do not have a decimal for example 1359


    I need this to show as 135900


    Any ideas? I am playing with it now.

    Thanks again, - AB
    Attached Thumbnails Attached Thumbnails newpic.JPG  

  7. #7
    Join Date
    Aug 2004
    Posts
    173

    one last issue

    there is also one more issue.

    some of the data shows up as 6923.1 - eliminating the trailing 0

    I need this to show as 692310

    Thanks - AB

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    =int(myvalue*100)

    or possibly
    =fix(myvalue*100)

  9. #9
    Join Date
    Aug 2004
    Posts
    173

    one last issue

    there is also one more issue.

    some of the data shows up as 6923.1 - eliminating the trailing 0

    I need this to show as 692310

    Thanks - AB

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe you have accidentally set the ignore flag on healdem's posts so i repeat what he said:

    what's wrong with *100 ?
    as in
    =int(myvalue*100)
    or possibly
    =fix(myvalue*100)
    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    abinboston...it doesn't get any easier than that:

    GrossWages_Q4 = Int(GrossWages_Q4 * 100)

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •