$$ rounding down $$

[toddbingham]

Have currency coming over from a link table. Appending it to a table, running a query to select certain records off of the table. Have a report that makes it look pretty that is ran from the query. I need to be able to take the number (say $553.54) and have it round down to the nearest five dollar increment with .00 cents. So, I would need it to look like 550.00, how would I acheive this?

Thanks.

 

[Mile-O]

1. Divide it by 10
2. Use the Int function
3. Multiply by 10
4. Use the CCur function

i.e.

1. $553.54 / 10 = 55.354
2. Int(55.354) = 55
3. 55 * 10 = 550
4. CCur(550) = $550.00

Altogether, now:
=CCur(Int(MyMoney/10)*10)

 

[dcx693]

Altogether, now:
=CCur(Int(MyMoney/100)*100)

This version seems to work:
=CCur(Int(MyMoney/10)*10)

We know what you really meant Mile!

 

[Mile-O]

lol, i've been getting by zeros mixed up all this week.

I'm sure I meant by 10 and not 100 - I'm just losing the plot..!!

Craftily edited it...

 

[st3ve]

Round down to multiple of 5

.....round down to the nearest five ...

ie. 554.43 to 550.00
557.76 to 555.00 etc

try this ... NewValue = 5 *int([OriginalValue]/5 )

HTH

 

[Mile-O]

Good point!

 

[dcx693]

Oh details, details....

 

[toddbingham]

Alright, both of these formulas work, but they do not give me the .00 on the end.
Any ideas? I can live without it, but it sure would be nice to have the .00 on there.

Thanks.

 

[Mile-O]

CCur(5 *Int([OriginalValue]/5 ))

 

[toddbingham]

That does not do it either. I also added an Inout mask of 0000.00;0;0
this added to . to the end but not the 0's

 

[st3ve]

.00

Oh yeah! Forgot that bit!

In the query, where you put in the formula to do the rounding, make sure that the properties of this new field (in the query) are set to Currency or Fixed.

 

[toddbingham]

Still did not work, tried both Currency and Fixed format.

 

[Mile-O]

This is getting ridiculous...

Format(5 *Int([OriginalValue]/5 ),"0.00")

 

[toddbingham]

Alright, I will stop. Sorry to bother you Mile.

 

[Mile-O]

lol, I wasn't referring to you but the amount of formulas that we're going through...

Did that last one work?

 

[raskew]

Hmm,

From the debug window:

fmt = "$ ###,###.00"
x = 553.54
x = int(x/5)*5
? format(x, fmt)
$ 550.00

Does that work?

Bob