Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 379

# Force a calculation to Round up using MS Access

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
ChrisTalbot
1 Solution

Commented:

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

Mark
0

Commented:
=INT(10*E14)/10+0,1*(INT(E14*10)<>E14*10)

too tired now, can someone please make it better?

// j
0

Commented:
=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

Commented:
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

Commented:
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

Commented:
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 Commented:
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

Tackle projects and never again get stuck behind a technical roadblock.