Rounding to nearest .00 or .50

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form in which a particular field is determining the credit hours for
a course by dividing the total number of minutes of course instruction by 60
minutes (in some instances, 50 minutes, depending on the state). However, I
need the decimal number to round to the nearest half (either .00 or .50
ONLY), but I can only round to the nearest whole or either get the exact
number. For instance....660 minutes divided by a 50-minute hour is 13.20,
but I need it to round to the nearest .00 which, in this instance should be
13.00. Please help.
 
Access doesn't provide this functionality, but you can easily write your own
function, which you can either place in the form module, or since it is a
more general function, in a standalone application module (Module tab, New).
If you do the latter, you will need do declare it as a Public function as
shown below:

Public Function MyRounding(sglRawNumber As Single) As Single
Select Case sglRawNumber - Int(sglRawNumber)
Case Is >= 0.75
MyRounding = Int(sglRawNumber) + 1
Case Is < 0.25
MyRounding = Int(sglRawNumber)
Case Else
MyRounding = Int(sglRawNumber) + 0.5
End Select
End Function

Then set your textbox control source and/or calculated query field to the
function, passing the calculation as a parameter. Somehow you will need to
check for nulls or the function will return an error. Below is one option
using the IIf function:

=IIf(Not IsNull([txtRawNumber]) And Not
IsNull([txtDivisor]),MyRounding([txtRawNumber]/[txtDivisor]),"")


Hope that helps.
Sprinks
 
That MORE than helps!!! Thanks. And thank you for so quickly responding. I
spent so much time trying to manipulate Access into doing what I want, only
to have it return the desired number in some fields, but not others. Thanks
for getting me back on schedule.

I know where to go with my other questions, and believe me, I have a list,
as I am fairly new to Access and was thrown in without a paddle. Mind you,
no one here has a paddle or wants to even hear the word "Access". It's taboo
in this office. :-) But I'm stuck with the task, and have been making
progress in this massacred database the previous person left me with. So,
thanks a BUNCH for your help.

;-Dionne

Sprinks said:
Access doesn't provide this functionality, but you can easily write your own
function, which you can either place in the form module, or since it is a
more general function, in a standalone application module (Module tab, New).
If you do the latter, you will need do declare it as a Public function as
shown below:

Public Function MyRounding(sglRawNumber As Single) As Single
Select Case sglRawNumber - Int(sglRawNumber)
Case Is >= 0.75
MyRounding = Int(sglRawNumber) + 1
Case Is < 0.25
MyRounding = Int(sglRawNumber)
Case Else
MyRounding = Int(sglRawNumber) + 0.5
End Select
End Function

Then set your textbox control source and/or calculated query field to the
function, passing the calculation as a parameter. Somehow you will need to
check for nulls or the function will return an error. Below is one option
using the IIf function:

=IIf(Not IsNull([txtRawNumber]) And Not
IsNull([txtDivisor]),MyRounding([txtRawNumber]/[txtDivisor]),"")


Hope that helps.
Sprinks

DTenhue said:
I have a form in which a particular field is determining the credit hours for
a course by dividing the total number of minutes of course instruction by 60
minutes (in some instances, 50 minutes, depending on the state). However, I
need the decimal number to round to the nearest half (either .00 or .50
ONLY), but I can only round to the nearest whole or either get the exact
number. For instance....660 minutes divided by a 50-minute hour is 13.20,
but I need it to round to the nearest .00 which, in this instance should be
13.00. Please help.
Click to expand...
Click to expand...
 
My pleasure. I inherited a database also, and while the previous creator had
some very good ideas, it was not inherently normalized. I strongly urge you
to study your application structure, read up on database normalization, and
restructure your data to make it normalized. It will make your life so much
easier in the long run.

Good luck.
Sprinks

DTenhue said:
That MORE than helps!!! Thanks. And thank you for so quickly responding. I
spent so much time trying to manipulate Access into doing what I want, only
to have it return the desired number in some fields, but not others. Thanks
for getting me back on schedule.

I know where to go with my other questions, and believe me, I have a list,
as I am fairly new to Access and was thrown in without a paddle. Mind you,
no one here has a paddle or wants to even hear the word "Access". It's taboo
in this office. :-) But I'm stuck with the task, and have been making
progress in this massacred database the previous person left me with. So,
thanks a BUNCH for your help.

;-Dionne

Sprinks said:
Access doesn't provide this functionality, but you can easily write your own
function, which you can either place in the form module, or since it is a
more general function, in a standalone application module (Module tab, New).
If you do the latter, you will need do declare it as a Public function as
shown below:

Public Function MyRounding(sglRawNumber As Single) As Single
Select Case sglRawNumber - Int(sglRawNumber)
Case Is >= 0.75
MyRounding = Int(sglRawNumber) + 1
Case Is < 0.25
MyRounding = Int(sglRawNumber)
Case Else
MyRounding = Int(sglRawNumber) + 0.5
End Select
End Function

Then set your textbox control source and/or calculated query field to the
function, passing the calculation as a parameter. Somehow you will need to
check for nulls or the function will return an error. Below is one option
using the IIf function:

=IIf(Not IsNull([txtRawNumber]) And Not
IsNull([txtDivisor]),MyRounding([txtRawNumber]/[txtDivisor]),"")


Hope that helps.
Sprinks

DTenhue said:
I have a form in which a particular field is determining the credit hours for
a course by dividing the total number of minutes of course instruction by 60
minutes (in some instances, 50 minutes, depending on the state). However, I
need the decimal number to round to the nearest half (either .00 or .50
ONLY), but I can only round to the nearest whole or either get the exact
number. For instance....660 minutes divided by a 50-minute hour is 13.20,
but I need it to round to the nearest .00 which, in this instance should be
13.00. Please help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top