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
  • Last Modified:

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
Asked:
ChrisTalbot
1 Solution
 
stiemarkCommented:
Add .05 before rounding:

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


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

too tired now, can someone please make it better?

// j
0
 
njelgerCommented:
=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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
njelgerCommented:
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
 
RiverGuyCommented:
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
 
stiemarkCommented:
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
 
ChrisTalbotAuthor 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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