Allen Browne - Rounding Up and Down

[A Hopper]

I was late responding to your answer regarding my Rounding
Up and Down question. I have downloaded Ken Getz' rounding
function from
http://www.mvps.oprg/access/modules/md10054.htm, however,
I do not understand how it works or how to call it.

On a form "SixDifinPackageForm" are textboxes "MasterQty"
and "NumberOfPositions" which I want to divide and round
up and down to the nearest 10, 100 etc.. When the form
opens the textboxes receive their values from a form
called "PackSetUp". The On Open event of
the "SixDifinPackageForm" get the value for "MasterQty"
and "NumberOfPositions" from a form called "PackSetUp". I
also want to use the On Open event to call Ken Getz'
rounding function.

Example:
"MasterQty" value is 2000, "NumberOfPositions" value is 6,
(Me.MasterQty)/(Me.NumberOfPositions) = 333.3
Round down to 300
Round up to 400


Dim MaxRequired As Single
Dim MinRequired As Single
MaxRequired = 400
MinRequired = 300

Rounding depends on the answer to (Me.MasterQty)/
(Me.NumberOfPosition)

20/6 = 3.3 Round up 4 Round down 3
200/6 = 33.3 Round up to 40 Round down to 30 (nearest 10)
2000/6 = 333.3 Round up to 400 Round down to 300
(nearest 100)

Even if Ken Getz' rounding is not what I need for what I
am trying to do I would like to understand how to use it.

Thanks for you help

Allan

 

[Allen Browne]

Ken's function from
http://www.mvps.org/access/modules/mdl0054.htm
takes these arguments:
Number = the number you want to round.
NumDigits = the number of decimal places.

The NumDigits is a power of 10.
Enter 2, and it rounds 2 places to the right of the decimal point
(hundredths)
Enter 3, and it rounds 3 places to the right (thousandths).
Enter -2, and it rounds 2 places to the *left* of the decimal (hundreds).
Enter -3, and it rounds 3 places to the left (thousands).

Let's assume you renamed the function to GetzRound() so it does not conflict
with the built-in one. To try it out, press Ctrl+G to open the immediate
window (ctrl+G).
To round 123456 to the nearest thousand:
? GetzRound(123456, -3)
To round 2000/6 to the nearest hundred:
? GetzRound(2000/6, -2)

The Open Event of the form does not sound like a useful place to apply this
function.
- If this is just for display, use a calculated field in your query.
- If you want to round and assign the result as the user enters it, use the
AfterUpdate event of the box where they may the entry.
- If you want to alter existing data, use the function in an Update query
(Update on Query menu in query design).

Hope that helps.

 

[A Hopper]

Allen, when I tried the function in the immediate window
using ? GetzRound(123456, -3) I get the error message
"Compile error : Argument Not Optional"
When I look at the function "Round" near the End Function
is highlighted.

' Finish the calculation.
Round = intSgn * Int(varTemp) / dblPower
End Function

This the the same error I was receiving when I tried the
function in my application.

Should I see the answer in the immediate window? If not
would I use a message box to see the answer?

Thank you for your patience with my lack of knowledge and
for your help.

Allan

 

[Allen Browne]

The line:
Round = intSgn * Int(varTemp) / dblPower
assigns the return value for the function.

If you renamed the function, you also need to rename what to assign the
return value to, i.e.:
GetzRound = intSgn * Int(varTemp) / dblPower

 

[A Hopper]

Allen
That was what I was missing. I didn't make the connection
between the function name and the answer when the function
finished running.It work just as I would like now.

Thank you again for you patience and help.
Allan