Forcing numbers to round up or down

[Guest]

I am building a form in which I need to make calculations. I need to ask a
calculated field with two decimal places to create
(1) a new number which is rounded UP to the nearest whole number if it is
over a pre-set whole number (even by .01) and
(2) a new number which is rounded DOWN to nearest whole number if it is less
than a pre-set whole number (even by .01).
Right now, if I "set value" to the new fields and change the demimals to 0,
the program chooses on its own whether to round up or down to whichever whole
number it is closer.

I need to round up, even if is .01 over, in one case,
and to round down, even if is over by .99 in another.

For instance:
Field 1: Round 67.01-67.99 to 68
Field 2: Round 67.01-67.99 to 67

Any ideas?

Thanks,
Kim Crenshaw

 

[Ken Snell [MVP]]

Field 1: Round 67.01-67.99 to 68
MyAnswer = -Int(-[Field 1])

Field 2: Round 67.01-67.99 to 67
MyAnswer = Int([Field 1])

 

[Guest]

Thank you Ken for your assistance.

Would I put the
MyAnswer = -Int(-[Field 1])
in the validation for that field or in the conditions for the SetValue?

So far, I am building a macro that When [SCT]>[RunTime], field named [Under]
is set to [RunTime]-[SCT] and field named [Over] is set to 0, but I need the
RunTime value rounded up to next highest whole number before that calculation.

By the same token,
When [SCT]<[RunTime], Over is set to [SCT]-[RunTime], and [Under] is set to
0, but, again, I need the RunTime value rounded down to nearest whole number
prior to the calculation.

So would I put the "MyAnswer = -Int(-[Under])"
in as a condition for the SetValue of the Macro portion or in the Properties
section of the new field itself? (In this case the [Under] field)

I have no objection to doing this as code or an expression, but do not know
how to do that.

Field 1: Round 67.01-67.99 to 68
MyAnswer = -Int(-[Field 1])

Field 2: Round 67.01-67.99 to 67
MyAnswer = Int([Field 1])

--

Ken Snell
<MS ACCESS MVP>

I am building a form in which I need to make calculations. I need to ask a
calculated field with two decimal places to create
(1) a new number which is rounded UP to the nearest whole number if it is
over a pre-set whole number (even by .01) and
(2) a new number which is rounded DOWN to nearest whole number if it is
less
than a pre-set whole number (even by .01).
Right now, if I "set value" to the new fields and change the demimals to
0,
the program chooses on its own whether to round up or down to whichever
whole
number it is closer.

I need to round up, even if is .01 over, in one case,
and to round down, even if is over by .99 in another.

For instance:
Field 1: Round 67.01-67.99 to 68
Field 2: Round 67.01-67.99 to 67

Any ideas?

Thanks,
Kim Crenshaw

 

[Guest]

You are most awesome Ken!
After posting my previous reply I set a two fields on the form. I used the
"= -Int(-[RunTime]" and "= Int([RunTime]" respectively as the control sources
for those fields and got exactly what I needed - one field rounding up to the
next whole number and one field rounding down. Thank you, thank you, thank
you!!!

Kim Crenshaw

Field 1: Round 67.01-67.99 to 68
MyAnswer = -Int(-[Field 1])

Field 2: Round 67.01-67.99 to 67
MyAnswer = Int([Field 1])

--

Ken Snell
<MS ACCESS MVP>

I am building a form in which I need to make calculations. I need to ask a
calculated field with two decimal places to create
(1) a new number which is rounded UP to the nearest whole number if it is
over a pre-set whole number (even by .01) and
(2) a new number which is rounded DOWN to nearest whole number if it is
less
than a pre-set whole number (even by .01).
Right now, if I "set value" to the new fields and change the demimals to
0,
the program chooses on its own whether to round up or down to whichever
whole
number it is closer.

I need to round up, even if is .01 over, in one case,
and to round down, even if is over by .99 in another.

For instance:
Field 1: Round 67.01-67.99 to 68
Field 2: Round 67.01-67.99 to 67

Any ideas?

Thanks,
Kim Crenshaw

 

[Ken Snell [MVP]]

Glad to hear it works. Do note, though, that the rounding will be reversed
if the original number is negative; in that situation, the one that rounded
down will round up, and the one that rounded up will round down.

--

Ken Snell
<MS ACCESS MVP>

You are most awesome Ken!
After posting my previous reply I set a two fields on the form. I used the
"= -Int(-[RunTime]" and "= Int([RunTime]" respectively as the control
sources
for those fields and got exactly what I needed - one field rounding up to
the
next whole number and one field rounding down. Thank you, thank you, thank
you!!!

Kim Crenshaw

Field 1: Round 67.01-67.99 to 68
MyAnswer = -Int(-[Field 1])

Field 2: Round 67.01-67.99 to 67
MyAnswer = Int([Field 1])

--

Ken Snell
<MS ACCESS MVP>

I am building a form in which I need to make calculations. I need to ask
a
calculated field with two decimal places to create
(1) a new number which is rounded UP to the nearest whole number if it
is
over a pre-set whole number (even by .01) and
(2) a new number which is rounded DOWN to nearest whole number if it is
less
than a pre-set whole number (even by .01).
Right now, if I "set value" to the new fields and change the demimals
to
0,
the program chooses on its own whether to round up or down to whichever
whole
number it is closer.

I need to round up, even if is .01 over, in one case,
and to round down, even if is over by .99 in another.

For instance:
Field 1: Round 67.01-67.99 to 68
Field 2: Round 67.01-67.99 to 67

Any ideas?

Thanks,
Kim Crenshaw

 

[Guest]

Good to know, Although it shouldn't happen in this case as these are for
recording times for dogs running an agility course. They will be either Null
or have some positive number. Thanks again very much.

Kim

Glad to hear it works. Do note, though, that the rounding will be reversed
if the original number is negative; in that situation, the one that rounded
down will round up, and the one that rounded up will round down.

--

Ken Snell
<MS ACCESS MVP>

You are most awesome Ken!
After posting my previous reply I set a two fields on the form. I used the
"= -Int(-[RunTime]" and "= Int([RunTime]" respectively as the control
sources
for those fields and got exactly what I needed - one field rounding up to
the
next whole number and one field rounding down. Thank you, thank you, thank
you!!!

Kim Crenshaw

Field 1: Round 67.01-67.99 to 68
MyAnswer = -Int(-[Field 1])

Field 2: Round 67.01-67.99 to 67
MyAnswer = Int([Field 1])

--

Ken Snell
<MS ACCESS MVP>


I am building a form in which I need to make calculations. I need to ask
a
calculated field with two decimal places to create
(1) a new number which is rounded UP to the nearest whole number if it
is
over a pre-set whole number (even by .01) and
(2) a new number which is rounded DOWN to nearest whole number if it is
less
than a pre-set whole number (even by .01).
Right now, if I "set value" to the new fields and change the demimals
to
0,
the program chooses on its own whether to round up or down to whichever
whole
number it is closer.

I need to round up, even if is .01 over, in one case,
and to round down, even if is over by .99 in another.

For instance:
Field 1: Round 67.01-67.99 to 68
Field 2: Round 67.01-67.99 to 67

Any ideas?

Thanks,
Kim Crenshaw