More than whole number to update to next number

[knighty03]

I have a query where it gives me quantities of materials. In it, the quantity might say i need 2.5 of a certain material, but I only deal in whole numbers. I need something where it will round up to the next whole number. Any help would be appreciated.

 

[dyarwood]

The following idea might be a bit long but might work

use

i = Int(x)

'x is any number

If i<x Then
i=i+1
End If

This means that if x is over an integer, the results, i is the next integer on.

dyarwood

 

[knighty03]

Tried it but with no joy. I need to set a parameter inside a query which is tied to a table to update. Any other suggestions.

 

[dyarwood]

Could you use the parameter within a SQL string in the vba code? If you post your code I might have a few better ideas.

dyarwood

 

[MakeItSo]

try this:

If Fix(Val(YourValue)<Val(YourValue) Then YourValue = Fix(Val(YourValue)+1)

the Fix command will cut off all decimals. So, adding 1 and cutting off the decimals will turn 2.5 to 3.5 and then to 3 which is what you'd like.

However, you must first check if it your value is not 2.0, that's what the if statement does.


OK?
makeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MakeItSo]

Hmpff - forgot a closing bracket:
If Fix(Val(YourValue))< Val(YourValue) Then YourValue = Fix(Val(YourValue)+1)

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[dyarwood]

The fix function acts the same as the int function apart from when it comes to negative numbers.

dyarwood

 

[MakeItSo]

Yepp. Mixed s.th. up. I think it's in C++ where Int rounds...
So our attempts are pretty identical...


Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[dyarwood]

In C++ Int does do the rounding down. Think that your way is a lot more concise than mine. However, apparently mine isn't working but I think that the method is the right one.

dyarwood

 

[MakeItSo]

Here's the same to implement in a query:

IIf(Int([hours]<[hours]),Int([hours])+1,[hours]) AS hrs

Replace [Hours] by the field value you want to round and use [hrs] as an expression that contains the rounded value.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos