Rounding Up

[JUDO]

I have the following expression in a query. This gets the order due date takes away todays date then goes through a series of calculations to work out the period in terms of a number of months (Assuming they are all 4 weeks, wrong I know)

((((([Blanket_One]![Due Date]-Date())/7))*5)/20)

My problem is fairly simple I think. I need the output from this expression to always round up to the nearest whole number.

Example (1.0009 would round up to 2)

In excel I can use the function Roundup, is there an equivalent in Access?

Thanks

Judo

 

[neileg]

Int((((([Blanket_One]![Due Date]-Date())/7))*5)/20)+1

 

[bvan]

Not quite that simple NIELEG. What if the number is a whole number (no decimals) to start with. In that scenario, 3 would become 4.

 

[Brianwarnock]

How about

IIF(((((([Blanket_One]![Due Date]-Date())/7))*5)/20)-Int(((((([Blanket_One]![Due Date]-Date())/7))*5)/20))>0,Int(((((([Blanket_One]![Due Date]-Date())/7))*5)/20)+1,((((([Blanket_One]![Due Date]-Date())/7))*5)/20))

Not sure about the number of parenthesis, but the idea is simple , take the rounded integer answer from the original if 0 then original whole number if not then follow Neil's answer.

Brian

 

[RuralGuy]

Maybe this function will be useful.

Public Function Roundup(InVal As Variant) As Variant
'-- Round Up InVal and strip any decimal value
If IsNumeric(InVal) Then
   If InVal > 0 Then
      '-- Positive numbers
      If Abs(Int(InVal)) <> Abs(InVal) Then
         '-- There is a decimal component to InVal - RoundUp
         Roundup = Int(InVal + 1)
      Else
         '-- No decimal component - just return an Integer
         Roundup = Int(InVal)
      End If
   Else
      '-- Negative numbers
      If Abs(Int(InVal)) <> Abs(InVal) Then
         '-- There is a decimal component to InVal - RoundUp
         Roundup = Int(InVal - 1)
      Else
         '-- No decimal component - just return an Integer
         Roundup = Int(InVal)
      End If
   End If
Else
   MsgBox "Input to RoundUp is *NOT* numeric!"
End If

End Function

 

[JUDO]

Rounding up

Thanks everyone for you help I'll test it out.