Number formatting

[Robert]

Presently in my formula I am using

sngIncr = (Me.TL - Me.MIN) / 17

How should I modify the formula (or what command should I
use) that would round of the number so no decimals show.

Additionally, is there a way that I can round up to the
nearest number ending in 5. E.g. if the results of mu
formula is 3072, I would like 3075 to be displayed.

Your assitance is appreciated.

Rob

-----Original Message-----
When you click, the actual value of the field will show. If you want the
value to be in whole numbers, then enter it into the field as whole numbers.
If it is a calculated field, then you will have to round the results.

The format is simply a way to change the way a number looks. The underlying
number is still the same.

Rick B


I have created a form and set the format of the fields to
currency with a 0 decimal places.

When I go into form view I see the value as set e.g. $1,234
When I click in the field the view changes to $1,234.8366

What properties do I set so the display stays at $1,234

Any helps would be appreciated.

Rob


.

..

 

[Adrian Jansen]

To just convert to no decimals, you can convert the result to an integer:

sngIncr = Fnt((Me.TL - Me.MIN) / 17)

One problem with this is that Int returns the next lower number, even if the
result is negative,
so -4.3 gets converted to -5.
To correctly handle negative numbers, use the Fix function.

sngIncr = Fix((Me.TL - Me.MIN) / 17)

This will round down regardless of the decimal, which may not be what you
want.
To do correct rounding, you should add half the last digit to the result,
and then take its integer

sngIncr = Fix((0.5+(Me.TL - Me.MIN) / 17))

this brings you to the next question, rounding to an arbitrary value. If
you multiply your result by a factor which brings the last digit to an
integer, do the round and convert to integer, then divide by the same
factor, you get what you want. For a round to the nearest 5, your factor
would become 0.2 ( so that the last digit becomes 1 for a 5 input )

sngIncr = Fix(0.5 + (0.2 *((Me.TL - Me.MIN) / 17)))/0.2

For display purposes, Access VBA allows you do do a lot of this by simply
converting the result to a formatted string.

So for instance if you wanted to hold the internal value as a single
sngIncr, but display it in a textbox on a form as txtResult, you could just
go

me!txtResult = format(sngIncr,"0")

This correctly rounds and converts the single to display a string with at
least 1 digit, and no decimals shown. By doing similar multiplies and
divides, you can get rounds at any point. Note that this works on the
displayed result, not on the internal values - it depends what exactly you
want to do here.


Does that help ?

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control