rounding up in access

W

Wayne

Hi

I have a value that is less than 0 in a query and would like it to round up
to 1.

Please advise and thank you in advance.
 
A

Arvin Meyer [MVP]

Use a function such as (aircode):

Public Function NoZero(varIn As Variant) As Variant
On Error Resume Next

If IsNumeric(varIn) Then
If varIn <=0 Then
varIn = 1
End If
Else
varIn = Null
End If

End Function

put that in a standard module and call it in a query like:

NewValue: NoZero([Original FieldName])
 
J

Jeff Boyce

Maury

-2437 is less than 1 ... do you want to round to +1?

If you have "a value that is less than 0", why do you want a +1?

What are you working with (a bit more context might help folks offer
suggestions)?

Have you looked into using the IIF() function?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
W

Wayne

Sorry i'm just a basic user what i meant to say is that I have a value that
is less than 1 i.e 0.24 for instance and I want to round it up to +1.
 
J

Jeff Boyce

Wayne

If you format that value to display as an integer, I suspect it will show
0.24 as 1.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

datAdrenaline

To round UP to the nearest WHOLE number I use the following expression:

-Int(-YourValue)

Example:

-Int(-0.24) = 1

I actually have a User Defined Function named Ceiling() {since that is the
name of similar functions in other systems} ...

Public Function Ceiling(dblValue As Double) As Currency
Ceiling = -Int(-dblValue)
End Function

Note ... the reason I use Currency as the return datatype is to get a larger
range of return numbers ... but you can use what seem appropriate (Long,
Integer, Byte).

HTH ...
 
K

krisburana

DATADREALINE

Your a god spot on TY!!!

datAdrenaline said:
ToroundUPto the nearest WHOLE number I use the following expression:


-Int(-0.24) = 1
Click to expand...
I actually have a User Defined Function named Ceiling() {since that is the
name of similar functions in other systems} ...
Click to expand...
Public Function Ceiling(dblValue As Double) As Currency
Ceiling = -Int(-dblValue)
End Function
Click to expand...
Note ... the reason I use Currency as the return datatype is to get a larger
range of return numbers ... but you can use what seem appropriate (Long,
Integer, Byte).
Click to expand...
HTH ...
Click to expand...
Click to expand...

I followed your instructions, but for some reason it will just round
down with the INT function....for instance I'm calculating time units
in 15 minute intervals which means that 45 minutes would be 3 but
trying to get 46 minutes to equal 4....is there another function I
should use? Thanks.
 
D

Douglas J. Steele

I followed your instructions, but for some reason it will just round
down with the INT function....for instance I'm calculating time units
in 15 minute intervals which means that 45 minutes would be 3 but
trying to get 46 minutes to equal 4....is there another function I
should use? Thanks.
Click to expand...

What's the actual code you're using? You must have altered Brent's code,
since it would convert any amount of minutes to 1 hour.
 
Top