?
Solved

Force a calculation to Round up using MS Access

Posted on 2003-03-10
7
Medium Priority
?
369 Views
Last Modified: 2010-05-18
I need to force a simple calculation to round up to the nearest tenth.
currently, I am using round([Time]/60,1)
data examples:
37/60 - answer .6, want .7
61/60  - answer 1.0, want 1.1
ms excel uses function =roundup(37/60,1)


0
Comment
Question by:ChrisTalbot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Accepted Solution

by:
stiemark earned 300 total points
ID: 8106388
Add .05 before rounding:

round(([Time]/60) + 0.05, 1)


Mark
0
 
LVL 1

Expert Comment

by:njelger
ID: 8106403
=INT(10*E14)/10+0,1*(INT(E14*10)<>E14*10)

too tired now, can someone please make it better?

// j
0
 
LVL 1

Expert Comment

by:njelger
ID: 8106411
=INT(10*[CALC])/10+0,1*(INT([CALC]*10)<>[CALC]*10)

where [CALC] = [TIME]/60

too tired now, can someone please make it better?

// j
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:njelger
ID: 8106443
maybe i am sleepy but does "round(([Time]/60) + 0.05, 1)" work with 24/60 for example? doesn't it round up when it shouldn't ?
0
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8106500
Why not just use Excel?

Make a function in a module:

Public Function XCel(Fig1 As String, Fig2 As String) As Double
    Dim ex As New Excel.Application
    ex.Workbooks.Add
    ex.Worksheets.Add
    ex.Range("A2").Value = "=RoundUp(" & Fig1 & "/" & Fig2 & " ,1)"
    Dim dbl1 As Double
    dbl1 = ex.Range("A2").Value
    XCel = dbl1
End Function

Then use that function in your query:

SELECT XCel(37, 60)
FROM Table1;
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8106501
SELECT 24/60, round(24/60, 1), round( (24/60) + 0.05, 1)
gives: 0.4, 0.4, 0.4

What you're worried about is if it's 30 seconds:
round(30/60 + 0.05) = 0.6, not 0.5

Since we're dealing with only 60 possibilities (x minutes + 1 second, x minutes + 2 seconds, etc) then we can play with the rounding to always get what we want:

SELECT round((x/60) + 0.049, 1)

If you were dealing with numbers of greater precision, then we couldn't do this.


Mark
0
 

Author Comment

by:ChrisTalbot
ID: 8106522
thank you, elegant answer.  i tried it in excel and it works with one adjustment, value needs to be .049 instead of .05, thanks or your prompt help!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question