Rounding down to nearest 100

[SantasLittleHelper]

I have a calculated field with the following Expression:

Round([RecNo],-2)

The RecNo field is a regular autonumber field. I want the new calculated field to round the autonumber down to the nearest 100.

E.g.

[TABLE="width: 500"]
<tbody>[TR]
[TD]AutoNumber[/TD]
[TD]Calculated Field[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]399[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]1542[/TD]
[TD]1500[/TD]
[/TR]
</tbody>[/TABLE]


With the current expression, I get #func ! error in each record

 

[xenou]

Looks like MSAccess doesn't accept negative inputs for the second parameter. I don't think round would work anyway (since you would be rounding up or down based on the value - you really want a floor function here).

You can try:
(Int([RecNo]/100))*100

Not tested for negative RecNo (which I assume don't exist).
---------------
|    1 |    0 |
---------------
|  401 |  400 |
|   78 |    0 |
|  150 |  100 |
|  399 |  300 |
| 1542 | 1500 |
|  400 |  400 |
|    0 |    0 |
---------------

Further rounding info:
http://allenbrowne.com/round.html

 

[SantasLittleHelper]

Perfect! Thanks so much

 

[Joe4]

If this is related to your other question, regarding trying to break up your records in groups of 100, note the dangers of using an Autonumber field to do that (as I mentioned in your other thread).