Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2005
    Posts
    19

    Unanswered: Round off decimal

    i have in the control source the code

    =Dsum("[Working]", "EDITBOMDET")

    which is the summation of the calculated field [Working] from the query "EDITBOMDET"

    I want to round off this value to the next higher whole number but couldn't.
    I've changed the Decimal places properties to 0, i've tried using the command round(Dsum("[Working]", "EDITBOMDET")) too but was unsuccessful.
    what can i do?


    thank you

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't think of a way to do this directly other than using extremely inefficient multiple calls to dsum() along the lines
    = iif(int(dsum()) < dsum(), int(dsum())+1, int(dsum()))

    how about a hidden textbox = dsum()
    and your visible box = iif(int(hiddenbox) < hiddenbox, int(hiddenbox)+1, int(hiddenbox))

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by CRF
    i have in the control source the code

    =Dsum("[Working]", "EDITBOMDET")

    which is the summation of the calculated field [Working] from the query "EDITBOMDET"

    I want to round off this value to the next higher whole number but couldn't.
    I've changed the Decimal places properties to 0, i've tried using the command round(Dsum("[Working]", "EDITBOMDET")) too but was unsuccessful.
    what can i do?


    thank you
    Hi

    I'd be astonished if no one has a more elegant solution than this but:
    Code:
     	 Dim d As Double
    	
    	d = 3.66
    	
    	If CInt(d) - d = 0 Then
    		d = (d \ 1)
    	Else
    		d = (d \ 1) + 1
    	End If
    	
    	Debug.Print d
    Note the use of the \ operator as opposed to /.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - Hi Izy - that's what happens when you sit on a post for a while before committing.

    http://zones.advisor.com/doc/08884
    is a similar approach to mine but more advanced and flexible.

    Another here that looks a bit clumsy but similar idea
    http://p2p.wrox.com/topic.asp?TOPIC_ID=34330
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by pootle flump
    I'd be astonished if no one has a more elegant solution
    How about:

    d = -Int(-d)

    Works for d >= 0.
    Int() rounds down negative numbers, as in Int(-4.2) = -5.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ivon
    How about:

    d = -Int(-d)

    Works for d >= 0.
    Int() rounds down negative numbers, as in Int(-4.2) = -5.
    Nice job Ivon - You have ensured that this morning at least will be astonishment free Doesn't it work for negatives too? -4.2 becomes Int(4.2) -> -4.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2002
    Posts
    272

    Thumbs up

    You're right, -4 is the 'next higher whole number' to -4.2. I mistakenly thought we wanted -5 in that case.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want to round up then
    int(myvalue+0.5)
    OR if you are concerned about negative vlaue rounding
    if myvalue<0 then fix(myvalue-0.5) else int(myvalue+0.5)

    the .5 forces myvalue to round up (or down if negative)

    effectively it bcomes
    =fix(Dsum("[Working]", "EDITBOMDET")-0.5)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    if you want to round up then
    int(myvalue+0.5)
    OR if you are concerned about negative vlaue rounding
    if myvalue<0 then fix(myvalue-0.5) else int(myvalue+0.5)

    the .5 forces myvalue to round up (or down if negative)

    effectively it bcomes
    =fix(Dsum("[Working]", "EDITBOMDET")-0.5)
    Hi Healdon

    What if myValue is 7.2 (for example)?
    int(7.2+0.5) = 7

    Do you mean Cint?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    int() returns the integer part of the value
    fix() returns the integer part of a negative value, rounding down
    in soem cases you can also use round(myval+0.5,0)

    adding the 0.5 forces the valaue into the next band up where the original value is over .5 ie
    7.25+0.5 makes 7.75 makes int(7.75)=7
    7.75+0.5 makes 8.25 makes int(8.25)=8

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    **deleted**
    Last edited by ivon; 10-26-05 at 10:00. Reason: quoted below instead of edit

  12. #12
    Join Date
    Nov 2002
    Posts
    272
    True, but CRF wants 'to round off this value to the next higher whole number', so 7.25 needs to be rounded up to 8.

    CRF tried the Round() function, but that does the same as what you are proposing, and not what he wants.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so I get a little confused bouncing around the various languages I end up supporting:-

    if you change the factor to +.4999999999999999 then round(<expression>+factor,0) gives the required rounding up

    I'm not too happy with the .4999999999999999 as I'm huessing its to do with the intrinsic data types in VBA running on a 32 bit machine - they may change with a 64 bit platform.

    alternatively you could test to see if the integer portion of the original number was the same as the original number (and if not add one (or subtract 1 if negative rounding foen is required))

  14. #14
    Join Date
    Nov 2002
    Posts
    272
    I agree the .4999... doesn't look too good, but your alternate solution should work perfectly.
    I find my -Int(-<expr>) more elegant though :-)

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    alternatively you could test to see if the integer portion of the original number was the same as the original number (and if not add one (or subtract 1 if negative rounding foen is required))
    isn't that where i started?

    but there is no need to look beyond ivon's little trick. so cute!

    izy
    currently using SS 2008R2

Posting Permissions

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