Force Currency to end in .00

[Ember]

I have a "Total" field on a report that is pulled from a query. This "total"
is calculated in the underlying query by adding several fields from related
queries. There are times that I need that total to show the "exact"
calculated amount, ie. 1,250.78, however on my report I need this total to be
shown as $1,251.00.

I have been able to have the report show $1,251, but it doesn't include the
".00". I have changed the formatting, but only am able to get $1,250.78 to
show.

I have read some posts on rounding, but they seem more complicated than I
feel comfortable using. Is there a simple solution for my need?

Thanks!
Ember

 

[John Spencer MVP]

Assuming that there is always a value you could use one of these as the
control source. Just be sure the control is not named the same as the field
involved.

=Format(CLng(TheNumber),"Currency")

Or better yet use
= Format(Round(TheNumber),"Currency")



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have a "Total" field on a report that is pulled from a query. This "total"
is calculated in the underlying query by adding several fields from related
queries. There are times that I need that total to show the "exact"
calculated amount, ie. 1,250.78, however on my report I need this total to be
shown as $1,251.00.

I have been able to have the report show $1,251, but it doesn't include the
".00". I have changed the formatting, but only am able to get $1,250.78 to
show.

I have read some posts on rounding, but they seem more complicated than I
feel comfortable using. Is there a simple solution for my need?

Set the report text box's Format property to something like:
$#,##0

 

[Larry Linson]

You do realize that by showing the decimal places, you imply that the
information is accurate to those digit positions, don't you? Thus, showing
a rounded or truncated currency value, but forcing "00" in the cents
position is misleading to the readers of the report. A rounded or truncated
value _should_ be shown without the decimal positions.

Larry Linson
Microsoft Office Access MVP

 

[Ember]

This report is for internal billing purposes and we do not charge the "cents"
to the other departments, only the rounded up value. This practice is known
and agreed upon by those involved with this process.

Thanks for your suggestion.

Ember