Solved

# Force a calculation to Round up using MS Access

Posted on 2003-03-10
Medium Priority
369 Views
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
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

LVL 2

Accepted Solution

stiemark earned 300 total points
ID: 8106388

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

Mark
0

LVL 1

Expert Comment

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

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

LVL 1

Expert Comment

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

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.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

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

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

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 â€¦