By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Round numbers to integer

P: n/a
I am looking for a way to round down the results of a calculation in a
query

f.e.:

in a query this calculation is performed:

a/b = c

5/3 = 1,666666667

I would like this to be rounded to 1, the lower nearest integer. How
would I do this in a query????

TIA HENRO

Mar 19 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Hi,
I am looking for a way to round down the results of a calculation in a
query

f.e.:

in a query this calculation is performed:

a/b = c

5/3 = 1,666666667

I would like this to be rounded to 1, the lower nearest integer. How
would I do this in a query????
You can use int or fix - e.g. int(5/3)
To be aware of the differences of it take a look at the online help.

Regards
Jens

Mar 19 '07 #2

P: n/a
On Mar 19, 7:26 am, "Henrootje" <Hen...@gmail.comwrote:
I am looking for a way to round down the results of a calculation in a
query

f.e.:

in a query this calculation is performed:

a/b = c

5/3 = 1,666666667

I would like this to be rounded to 1, the lower nearest integer. How
would I do this in a query????

TIA HENRO


Something else you could do is 5\3 (Slash in the opposite direction).

Options!!!

Mar 19 '07 #3

P: n/a
IMHO, absolutely the best rounding function that I have ever seen.

Option Compare Database 'Use database order for string comparisons
Option Explicit
Global Const vb_roundup = 1
Global Const vb_rounddown = 0

Function RoundToNearest(Amt As Double, RoundAmt As Variant, Direction As
Integer) As Double
On Error Resume Next
Dim Temp As Double
Temp = Amt / RoundAmt
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
If Direction = vb_rounddown Then
Temp = Int(Temp)
Else
Temp = Int(Temp) + 1
End If
RoundToNearest = Temp * RoundAmt
End If
End Function
========================

Usage:

roundtonearest(5/3,1,1)
Result:1

RoundToNearest(0.6723983761,.000005,0)
Result: 0.672395

========================

"Matt" <md*******@yahoo.comwrote in message
news:11**********************@l75g2000hse.googlegr oups.com...
On Mar 19, 7:26 am, "Henrootje" <Hen...@gmail.comwrote:
I am looking for a way to round down the results of a calculation in a
query

f.e.:

in a query this calculation is performed:

a/b = c

5/3 = 1,666666667

I would like this to be rounded to 1, the lower nearest integer. How
would I do this in a query????

TIA HENRO

Something else you could do is 5\3 (Slash in the opposite direction).

Options!!!

Mar 20 '07 #4

P: n/a
"Don Leverton" <le****************@telusplanet.netwrote in
news:1cHLh.64305$lY6.48607@edtnps90:
IMHO, absolutely the best rounding function that I have ever seen.

Option Compare Database 'Use database order for string comparisons
Option Explicit
Global Const vb_roundup = 1
Global Const vb_rounddown = 0

Function RoundToNearest(Amt As Double, RoundAmt As Variant, Direction
As
Integer) As Double
On Error Resume Next
Dim Temp As Double
Temp = Amt / RoundAmt
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
If Direction = vb_rounddown Then
Temp = Int(Temp)
Else
Temp = Int(Temp) + 1
End If
RoundToNearest = Temp * RoundAmt
End If
End Function
========================

Usage:

roundtonearest(5/3,1,1)
Result:1

RoundToNearest(0.6723983761,.000005,0)
Result: 0.672395
How many others have you seen?

Wouldn't it be great to have a function that could round verbose
functions down to one line?

I guess I'm not so hot at math ... let's see ... 5/3 rounded up ... to
the nearest integer (maybe that's not what that means ... what does
RoundAmt mean anyhow? and why is it a variant? ... did you mean
deviant?)... is one? Hmmmmmmm! Guess the old mind is just not working
tonight, not to mention .... Sorry, Larry, didn'tmean to embarass you
there.
Mar 20 '07 #5

P: n/a
On Mar 19, 10:13 pm, lyle fairfield <lylef...@yahoo.cawrote:
"Don Leverton" <leveriteNoJunkM...@telusplanet.netwrote innews:1cHLh.64305$lY6.48607@edtnps90:


IMHO, absolutely the best rounding function that I have ever seen.
Option Compare Database 'Use database order for string comparisons
Option Explicit
Global Const vb_roundup = 1
Global Const vb_rounddown = 0
Function RoundToNearest(Amt As Double, RoundAmt As Variant, Direction
As
Integer) As Double
On Error Resume Next
Dim Temp As Double
Temp = Amt / RoundAmt
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
If Direction = vb_rounddown Then
Temp = Int(Temp)
Else
Temp = Int(Temp) + 1
End If
RoundToNearest = Temp * RoundAmt
End If
End Function
========================
Usage:
roundtonearest(5/3,1,1)
Result:1
RoundToNearest(0.6723983761,.000005,0)
Result: 0.672395

How many others have you seen?

Wouldn't it be great to have a function that could round verbose
functions down to one line?

I guess I'm not so hot at math ... let's see ... 5/3 rounded up ... to
the nearest integer (maybe that's not what that means ... what does
RoundAmt mean anyhow? and why is it a variant? ... did you mean
deviant?)... is one? Hmmmmmmm! Guess the old mind is just not working
tonight, not to mention .... Sorry, Larry, didn'tmean to embarass you
there.- Hide quoted text -
I still haven't stopped laughing. Since you asked for a one-
liner :-),

Function RoundToNearest(NearestAmt As Double, ValueToRound As Double,
Direction As String) As Double
RoundToNearest = IIf(Direction = "Up", -NearestAmt * Int(-
ValueToRound / NearestAmt), NearestAmt * Int(ValueToRound /
NearestAmt))
End Function

Example:

RoundToNearest(1, 5 / 3, "Up") =2
RoundToNearest(1, 5 / 3, "Down") =1
RoundToNearest(15, -1, "Down") =-15

The basic ideas are outlined in:
http://groups.google.com/group/micro...14be58d0485dd0

but test before using anyway. Note that it does not round verbose
functions down to one line as Lyle requested.

James A. Fortune
CD********@FortuneJames.com

P.S., I noticed that my CDMA postings average a two star rating out of
five. I noted that Lyle also has an average rating of two stars and I
felt like I was in good company. After viewing the profiles of a few
other regular posters I concluded that the stars seem to have worked
out to be in proportion to how much we push MS products :-).
Mar 20 '07 #6

P: n/a
P.S., I noticed that my CDMA postings average a
two star rating out of five. I noted that Lyle also
has an average rating of two stars and I felt like I
was in good company. After viewing the profiles
of a few other regular posters I concluded that the
stars seem to have worked out to be in proportion
to how much we push MS products :-).
Rated where, by whom, Jim?

I'm probably going to be embarrassed when I find out and take a look... and
not for the reason Lyle meant, if he meant what I thought he meant, which
isn't necessarily the case.

And, I agree with you that Lyle's posts are generally a "good read". And,
hey, any guy who has a fondness for redheads can't be _ALL_ bad.

Larry
Mar 22 '07 #7

P: n/a
Hi, Larry.
Rated where
Google Groups. Please see the following Web pages for the average rating on
a few Google profiles:

James's:

http://groups.google.com/groups/prof...JdoyCsxg&hl=en

Lyle's:

http://groups.google.com/groups/prof...5IG9SZ-Q&hl=en

Yours:

http://groups.google.com/groups/prof...QNTOMjnQ&hl=en
by whom
By anyone with a Google Groups account who cares to rate a poster's answer
so that future newsgroup archive researchers will know whether or not a post
is worth reading. It's anonymous and completely subjective, so until lots
of people rate posts over a long period of time, one must take an individual
rating with a grain of salt. It's rather like Amazon.com, where if a new
book has only one review from a reviewer who panned it, one can't take that
as Gospel that it's a crappy book. However, if there are four or more
reviews from different people and they all gave it only one star, then it's
fairly obvious that the book isn't worth buying.
I'm probably going to be embarrassed when I find out and take a look
Don't be. Almost none of the Access MVP's are aware of the publicly
available tools to rate their answers. Since it's subjective and hardly
anyone is using Google's rating system yet, and it's fairly new, the numbers
can be pretty skewed. As always, the few complainers cry out far louder
than the satisfied customers who give pats on the back, even if the
satisfied customers outnumber the complainers 10 (or more) to 1.
And, I agree with you that Lyle's posts are generally a "good read".
I'm sure if everyone took the time to rate his answers, he'd have mostly
five stars. For all we know, it could be one disgruntled poster marking the
handful of Lyle's answers with two stars as "below average" because he
didn't like Lyle's attitude -- or because he hates all redheads and people
fond of redheads.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Larry Linson" <bo*****@localhost.notwrote in message
news:RooMh.12547$e47.7878@trnddc05...
P.S., I noticed that my CDMA postings average a
two star rating out of five. I noted that Lyle also
has an average rating of two stars and I felt like I
was in good company. After viewing the profiles
of a few other regular posters I concluded that the
stars seem to have worked out to be in proportion
to how much we push MS products :-).

Rated where, by whom, Jim?

I'm probably going to be embarrassed when I find out and take a look...
and not for the reason Lyle meant, if he meant what I thought he meant,
which isn't necessarily the case.

And, I agree with you that Lyle's posts are generally a "good read". And,
hey, any guy who has a fondness for redheads can't be _ALL_ bad.

Larry


Mar 22 '07 #8

P: n/a
Thanks, Gunny. I am, it turns out, not embarrassed, but pleasantly
surprised, at how well my posts are rated.

Larry
Mar 22 '07 #9

P: n/a
On Mar 22, 4:24 pm, "Larry Linson" <boun...@localhost.notwrote:
Thanks, Gunny. I am, it turns out, not embarrassed, but pleasantly
surprised, at how well my posts are rated.

Larry
Larry,

http://groups.google.com/group/comp....s-access/about

shows that with 12404 postings to CDMA you have the most all time
postings to this NG.

James A. Fortune
CD********@FortuneJames.com

Main email addresses I've used to post:

ja******@oakland.edu (out of service)
ji********@compumarc.com (out of service)
CD********@FortuneJames.com
MP*******@FortuneJames.com

Mar 23 '07 #10

P: n/a
<CD********@FortuneJames.comwrote
http://groups.google.com/group/comp....s-access/about

shows that with 12404 postings to CDMA you have the most all time
postings to this NG.
That's not nearly all of my posts... just under one e-mail (that I haven't
used in almost 3 years).

Larry
Mar 23 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.