How to Round Up to 0.5/1 ?

[edisonl via AccessMonster.com]

Hi,

I have a leave application that does leave updates, reset per year.

Situation:
For new staff Working less than a year 'Special' Formula is applied, where
there is 'surplus'.
Eg: Balance after applied formula= 2.1

Problem:
I like to do a round up & round down by 0.5.
Eg: 2.1 to 2.5, 2.6 to 3

Also how do I check if it is whole number (is it call absolute number or
stuffs like that) or with a decimal behind?

Edison

 

[Tom van Stiphout]

On Thu, 15 Jan 2009 03:34:05 GMT, "edisonl via AccessMonster.com"

You could write your own function to do that. But let's first agree on
the algorithm. I am surprised you said that you want to round up (see
subject line), round up & round down by 0.5, and from your examples it
seems you want to round up to the nearest 0.5.

To check if a number is a whole number, you can compare it with the
value when converted to Integer (or Long if you have large numbers):
dim sng as single
sng=2.1
if sng = cint(sng) then
msgbox "The value " & sng & " is a whole number"
else
msgbox "The value " & sng & " is NOT a whole number"
end if

To get just the fractional value, you subtract the two:
dim frac as single
frac = sng - cint(sng)

-Tom.
Microsoft Access MVP

 

[edisonl via AccessMonster.com]

Hi Tom,

But I cant get the value still.

My Original field datatype= Single in my Table.

So system gotta check if its more/less than 0.5

What I did was doing a comparison so after dividing by formula its value
become astonishing amount.
serious enough => -8.333334E-02 *duh!

Really had no idea how to convert into 1 devimal point.
Edison

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * *

You could write your own function to do that. But let's first agree on
the algorithm. I am surprised you said that you want to round up (see
subject line), round up & round down by 0.5, and from your examples it
seems you want to round up to the nearest 0.5.

To check if a number is a whole number, you can compare it with the
value when converted to Integer (or Long if you have large numbers):
dim sng as single
sng=2.1
if sng = cint(sng) then
msgbox "The value " & sng & " is a whole number"
else
msgbox "The value " & sng & " is NOT a whole number"
end if

To get just the fractional value, you subtract the two:
dim frac as single
frac = sng - cint(sng)

-Tom.
Microsoft Access MVP

Hi,

[quoted text clipped - 13 lines]

Edison

 

[John W. Vinson]

Hi Tom,

But I cant get the value still.

My Original field datatype= Single in my Table.

So system gotta check if its more/less than 0.5

What I did was doing a comparison so after dividing by formula its value
become astonishing amount.
serious enough => -8.333334E-02 *duh!

Really had no idea how to convert into 1 devimal point.

Edison, this has absolutely nothing to do with rounding to the nearest 0.5,
and your example doesn't either.

Rounding up to the next 0.5 can be done using:

-Int(-2*[field])/2

This will convert 0.01 to .5, 0.524 to 1.0, 1.000000001 to 1.5 and so on.

 

[edisonl via AccessMonster.com]

Hi John,

Erm.. pardon me but what would be the most appropriate wording beside round
off ?

Anyway The code "-Int()" what it can do ? Kindly enlighten me

Edison

Hi Tom,

[quoted text clipped - 9 lines]

Really had no idea how to convert into 1 devimal point.

Edison, this has absolutely nothing to do with rounding to the nearest 0.5,
and your example doesn't either.

Rounding up to the next 0.5 can be done using:

-Int(-2*[field])/2

This will convert 0.01 to .5, 0.524 to 1.0, 1.000000001 to 1.5 and so on.

 

[Maury Markowitz]

Anyway The code "-Int()" what it can do ? Kindly enlighten me

Int() cuts off any decimals.

0.1 => 0
0.2 => 0
....
1.1 => 1
1.2 => 1
....
2.1 => 2
2.2 => 2
etc.

Maury

 

[John W. Vinson]

Hi John,

Erm.. pardon me but what would be the most appropriate wording beside round
off ?

You said

What I did was doing a comparison so after dividing by formula its value
become astonishing amount.
serious enough => -8.333334E-02 *duh!

That seems to be a very reasonable Single number result (you do understand
that it's just scientific notation for -0.08333334, or -1/12, right?

Anyway The code "-Int()" what it can do ? Kindly enlighten me

The Int() function truncates a number to the next lowest integer (i.e.
Int(2.25) returns 2, Int(-2.24) returns -3). Therefore to round UP rather than
down, take the negative of the number.

My code multiplies the provided number by 2, rounds up to the next integer,
and then divides back by 2.

Suppose you have 1.32 in your number field. Multiplying by -2 gives you -2.64.
Int(-2.64) is -3 - the next lower integer. Dividing that by 2 gives -1.5, and
taking the negative of that gives 1.5. End result is to round UP 1.32 to 1.5.

Edison

Hi Tom,

[quoted text clipped - 9 lines]

Really had no idea how to convert into 1 devimal point.

Edison, this has absolutely nothing to do with rounding to the nearest 0.5,
and your example doesn't either.

Rounding up to the next 0.5 can be done using:

-Int(-2*[field])/2

This will convert 0.01 to .5, 0.524 to 1.0, 1.000000001 to 1.5 and so on.

 

[edisonl via AccessMonster.com]

Hi John,

I tried your method it works initially but... Let me give you an example..

My Value derived is 2.3 So tentatively would be round to 2.5.. but It gave me
only 2...
For vlaue of 2.91 it is suppose to round up to 3 but I got 2.5 instead...

The tricky part here is even so I can't write the value of 2.5 into my table!

I had check my field Data Type=Number(Single), Decimal Place=1,
Format=General Number
still unable to write. I even open up the table to manual input 2.5 into it
but it accept my input ! *puzzled

Edison

Hi John,

Erm.. pardon me but what would be the most appropriate wording beside round
off ?

You said

What I did was doing a comparison so after dividing by formula its value
become astonishing amount.
serious enough => -8.333334E-02 *duh!

That seems to be a very reasonable Single number result (you do understand
that it's just scientific notation for -0.08333334, or -1/12, right?

Anyway The code "-Int()" what it can do ? Kindly enlighten me

The Int() function truncates a number to the next lowest integer (i.e.
Int(2.25) returns 2, Int(-2.24) returns -3). Therefore to round UP rather than
down, take the negative of the number.

My code multiplies the provided number by 2, rounds up to the next integer,
and then divides back by 2.

Suppose you have 1.32 in your number field. Multiplying by -2 gives you -2.64.
Int(-2.64) is -3 - the next lower integer. Dividing that by 2 gives -1.5, and
taking the negative of that gives 1.5. End result is to round UP 1.32 to 1.5.

Edison

[quoted text clipped - 12 lines]

 

[John W. Vinson]

Hi John,

I tried your method it works initially but... Let me give you an example..

My Value derived is 2.3 So tentatively would be round to 2.5.. but It gave me
only 2...
For vlaue of 2.91 it is suppose to round up to 3 but I got 2.5 instead...

The tricky part here is even so I can't write the value of 2.5 into my table!

I had check my field Data Type=Number(Single), Decimal Place=1,
Format=General Number
still unable to write. I even open up the table to manual input 2.5 into it
but it accept my input ! *puzzled

Please post the SQL of your query and the relevant field definitions of your
table. It's not at all clear what you're doing - nothing I talked about would
"write a value" into your table, and you haven't mentioned this previously.

 

[edisonl via AccessMonster.com]

Hi,

It solves already... I didnt know the power of your negative value...
initially was my datatype that is suppose to be duplicate into table field
that is not set to single thats all....

Sorry for the misunderstanding, John..

Cheers & God Bless...

Edison

Hi John,

[quoted text clipped - 10 lines]

still unable to write. I even open up the table to manual input 2.5 into it
but it accept my input ! *puzzled

Please post the SQL of your query and the relevant field definitions of your
table. It's not at all clear what you're doing - nothing I talked about would
"write a value" into your table, and you haven't mentioned this previously.