rounding up in access

[Guest]

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.

 

[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])

 

[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

 

[Guest]

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.

 

[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

 

[Guest]

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 ...

 

[Guest]

DATADREALINE

Your a god spot on TY!!!

 

[krisburana]

DATADREALINE

Your a god spot on TY!!!

ToroundUPto the nearest WHOLE number I use the following expression:


-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 ...

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.

 

[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.

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.