Another time calculation issue, thanks for any help!

[James A. Fortune]

Just saw this thread. Here's one I've been using for more than 10 years that
rounds any amount in any direction:

Option Compare Database
Option Explicit
Public Const vb_roundup = 1
Public Const vb_rounddown = 0

Function Rnd2Num(Amt As Variant, RoundAmt As Variant, _
Direction As Integer) As Double
On Error Resume Next
If Direction = vb_rounddown Then
Rnd2Num = Int(CDec(Amt) / RoundAmt) * RoundAmt
Else
Rnd2Num = -Int(CDec(-Amt) / RoundAmt) * RoundAmt
End If
End Function

?Rnd2Num(1.09,.25,1)
1.25

Arvin,

The fact that you've used -Int(-X) for rounding up for over 10 years
suggests the possibility that Van T. Dinh borrowed that idea from
someone else many years ago.

C.f.:

http://groups.google.com/group/microsoft.public.access/msg/9614be58d0485dd0

and

http://groups.google.com/group/comp.databases.ms-access/msg/4b3e8b87e0ede4e4

Do you have any idea who discovered that gem originally? The level of
cleverness required to discover that is beyond what I see typically in
the newsgroups .

James A. Fortune
(e-mail address removed)

 

[Michel Walsh]

Ken Getz, among others, pointed out that NOT using CDec may lead to wrong
results (due to the problem of internal representation of a double float
number), even for relatively small number. The following example was from
Andy Baron ( November 8th, 1998) :


?Int(9.575*100+.5)
957
?Int(9.575*100+.5 & "")
958



Vanderghast, Access MVP

 

[Robert Morley]

Interesting, although in this case, you're using standard rounding rather
than rounding up or down; I suspect that with the addition, floating point
representation would be more of an issue here.

Still, it's something to consider. Thankfully, almost all of my
calculations are integer-based, so this is not an issue (and those that
aren't are currency-based).

Another possible consideration along these lines is whether to use, for
example:

-Int(-Amt * 4) / 4 'or
-Int(-Amt / 0.25) * 0.25

I suspect there could be some issues there if someone dug hard enough. Ah,
the joys of FP math on computers!



Rob

 

[Arvin Meyer [MVP]]

The fact that you've used -Int(-X) for rounding up for over 10 years
suggests the possibility that Van T. Dinh borrowed that idea from someone
else many years ago.

Do you have any idea who discovered that gem originally? The level of
cleverness required to discover that is beyond what I see typically in the
newsgroups .

Actually, I don't remember. It was so long ago (I started using it in Access
97). I'm pretty sure that I didn't write it originally because, My name
isn't anywhere in the code. I may have added the CDec after Access 2000, but
I can't remember that either. Code that I write that I reuse, generally has
my name on it. About 1/4 of the code I write in private applications has my
name. None of the newsgroup code that I write in the post itself has my name
(an often no error handling or testing), that's why I call it aircode.

 

[James A. Fortune]

Actually, I don't remember. It was so long ago (I started using it in Access
97). I'm pretty sure that I didn't write it originally because, My name
isn't anywhere in the code. I may have added the CDec after Access 2000, but
I can't remember that either. Code that I write that I reuse, generally has
my name on it. About 1/4 of the code I write in private applications has my
name. None of the newsgroup code that I write in the post itself has my name
(an often no error handling or testing), that's why I call it aircode.

Thanks Arvin. That's too bad. I don't usually sprinkle my name in my
reuse code, but perhaps that's a good way to remember where it originated.

James A. Fortune
(e-mail address removed)

 

[Gina Whipp]

ALWAYS put your name... helps people like me remember who to give credit to!

 

[Arvin Meyer [MVP]]

ALWAYS put your name... helps people like me remember who to give credit
to!

I don't want credit for bad code <lol>

Seriously, when you only have a limited amount of time, it saves time not to
bother with credits, error handling, etc. Besides, I have contributed so
much code for so long, in so many places, that it doesn't matter any more if
I get credit or not. I does rattle my cage if someone else takes credit for
it though.