How do I roundup to 2 places left of the decimal point?

[Guest]

I am new to Access and I need to round a dollar amount the to the next
greater $100.00, (example $18,770.07 needs to be round up to $18,800.00).
How can I do this?

 

[Allen Browne]

Try dividing by 1000, converting to integer, multiplying by 1000, and
converting back to currency:
CCur(1000 * CInt(Nz([Field1],0)/1000))

Unfortunately, the Round() function in Access cannot accept a negative
number of decimal places like the one in Excel can. If you want one that
can, you could copy this one and rename it so it does not fight with the
built-in one:
http://www.mvps.org/access/modules/mdl0054.htm

 

[Jamie Collins]

$18,800.00).

Try dividing by 1000, converting to integer, multiplying by 1000, and

converting back to currency:
CCur(1000 * CInt(Nz([Field1],0)/1000))

This is not quite what was asked for e.g. $18,440.04 and -$18,770.07
both get rounded down rather than up.

Take a look at this KB article:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

There is some detail about the different rounding algorithms used in MS
products.

Jamie.

--

 

[Guest]

Jamie,

That worked but you are correct that it is rounding down. Any suggestions
for novice. I will look the the Custom Rounding Article.

$18,800.00).

Try dividing by 1000, converting to integer, multiplying by 1000, and

converting back to currency:
CCur(1000 * CInt(Nz([Field1],0)/1000))

This is not quite what was asked for e.g. $18,440.04 and -$18,770.07
both get rounded down rather than up.

Take a look at this KB article:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

There is some detail about the different rounding algorithms used in MS
products.

Jamie.

 

[Allen Browne]

Sorry, mjv, I misread your question. You asked to round to the nearest $100,
and I gave you the nearest $1000.

Try multiplying and dividing by 100 instead of 1000:
CCur(100 * CLng(Nz([Field1],0)/100))

I've also suggested converting to long instead of integer, as it copes with
larger values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jamie,

That worked but you are correct that it is rounding down. Any suggestions
for novice. I will look the the Custom Rounding Article.

I need to round a dollar amount the to the next
greater $100.00, (example $18,770.07 needs to be round up to $18,800.00).

Try dividing by 1000, converting to integer, multiplying by 1000, and

converting back to currency:
CCur(1000 * CInt(Nz([Field1],0)/1000))

This is not quite what was asked for e.g. $18,440.04 and -$18,770.07
both get rounded down rather than up.

Take a look at this KB article:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

There is some detail about the different rounding algorithms used in MS
products.

Jamie.

 

[Guest]

Allen, No problem I figured out the $100 thing. How can I make roundup all
the time?

Sorry, mjv, I misread your question. You asked to round to the nearest $100,
and I gave you the nearest $1000.

Try multiplying and dividing by 100 instead of 1000:
CCur(100 * CLng(Nz([Field1],0)/100))

I've also suggested converting to long instead of integer, as it copes with
larger values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jamie,

That worked but you are correct that it is rounding down. Any suggestions
for novice. I will look the the Custom Rounding Article.

Allen Browne wrote:
I need to round a dollar amount the to the next
greater $100.00, (example $18,770.07 needs to be round up to
$18,800.00).

Try dividing by 1000, converting to integer, multiplying by 1000, and

converting back to currency:
CCur(1000 * CInt(Nz([Field1],0)/1000))

This is not quite what was asked for e.g. $18,440.04 and -$18,770.07
both get rounded down rather than up.

Take a look at this KB article:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

There is some detail about the different rounding algorithms used in MS
products.

Jamie.

 

[Allen Browne]

Not sure what you mean by "round up all the time".
The calculated field should always round up if you include the things you
have working.

If you are trying to store the result, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, No problem I figured out the $100 thing. How can I make roundup
all
the time?

Sorry, mjv, I misread your question. You asked to round to the nearest
$100,
and I gave you the nearest $1000.

Try multiplying and dividing by 100 instead of 1000:
CCur(100 * CLng(Nz([Field1],0)/100))

I've also suggested converting to long instead of integer, as it copes
with
larger values.

 

[Jamie Collins]

Not sure what you mean by "round up all the time".

The OP's original spec was clearer: "round a dollar amount the to the
next greater $100.00". So, $1 would round to the next greater $100. A
literal interpretation would also round -$99 to $0 and $100 to $200.

The calculated field should always round up if you include the things you
have working.

No, your suggested calculation rounds $1 to the lesser $0 and $-99 to
the lesser -$100.

I hesitate to suggest my own solution. Rather, I think the OP should
read the article I linked to
(http://support.microsoft.com/default.aspx?scid=kb;en-us;196652). There
may be a rounding algorithm (or actual function) there they can use. At
the very least the OP should give us some more test data and expected
results e.g. do negative values round towards zero? does $100 round to
the greater $200? etc.

Jamie.

--

 

[Allen Browne]

Ah, this kind of thing:
CCur(100 * - Int(Nz( - [Field1], 0) / 100))

 

[Guest]

I have a calculated field in a query with the following "SellNET:
CCur(100*CInt(Nz([gross sale price]+[Specials],0)/100)+[freight])". IF
"gross sales price" = $31,410.95 and "specials" = $0.00 and "freight" =
$4,000.00 the total would be $35,410.95. I would like to have Access round
this to $35,500.00. Access is looking at the second number to the left of
the decimal place which is "1" and giving me $35,400.00.

In Excel I could do this with the formula ROUNDUP((gross sale price +
specials),-2) + freight.

MJV

Not sure what you mean by "round up all the time".
The calculated field should always round up if you include the things you
have working.

If you are trying to store the result, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, No problem I figured out the $100 thing. How can I make roundup
all
the time?

Sorry, mjv, I misread your question. You asked to round to the nearest
$100,
and I gave you the nearest $1000.

Try multiplying and dividing by 100 instead of 1000:
CCur(100 * CLng(Nz([Field1],0)/100))

I've also suggested converting to long instead of integer, as it copes
with
larger values.