1. Registered User
Join Date
Aug 2005
Posts
19

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. Cavalier King Charles
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

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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

4. King of Understatement
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.

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

5. Registered User
Join Date
Nov 2002
Posts
272
Originally Posted by pootle flump
I'd be astonished if no one has a more elegant solution

d = -Int(-d)

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

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by ivon

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.

7. Registered User
Join Date
Nov 2002
Posts
272
You're right, -4 is the 'next higher whole number' to -4.2. I mistakenly thought we wanted -5 in that case.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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. Registered User
Join Date
Nov 2002
Posts
272
**deleted**
Last edited by ivon; 10-26-05 at 10:00. Reason: quoted below instead of edit

12. Registered User
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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. Registered User
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. Cavalier King Charles
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

#### Posting Permissions

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