Rounding numbers

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

I have a query that sums dollars amounts and what i would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this is a
query..
Thank you
 
Use the Round function as a wrapper around the Sum function:

Round(Sum([FieldName]), 0)
 
-----Original Message-----
I have a query that sums dollars amounts and what i would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this is a
query..
Thank you
.
Click to expand...

In your query multiply the amount times 100.
(Experiment with 10,100,1000, etc. for more decimal
places)
Add 0.4 to the amount to use the 4/5 up-down rounding.
Use the function "INT" to truncate the trailing digits
Divide the result by 100.

Here is an example:
Result:Int((
![Amount]*100)+0.4)/100)
Good Luck!
 
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Use the Round function as a wrapper around the Sum function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

Raj said:
I have a query that sums dollars amounts and what i would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this is a
query..
Thank you
Click to expand...
Click to expand...
 
Ken,
I am having a problem with this function. When i run the
query I display #Error for the fields that has cents that
should be rounded..
Thanks
-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Use the Round function as a wrapper around the Sum function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

I have a query that sums dollars amounts and what i would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this is a
query..
Thank you
Click to expand...
Click to expand...


.
Click to expand...
 
Sorry - looks like there were too many ( characters in the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Raj said:
Ken,
I am having a problem with this function. When i run the
query I display #Error for the fields that has cents that
should be rounded..
Thanks
-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Use the Round function as a wrapper around the Sum function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

I have a query that sums dollars amounts and what i would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this is a
query..
Thank you
Click to expand...


.
Click to expand...
Click to expand...
 
Ken,
I am not sure what is happening but I am still get the
#Error in the field.. Below is the SQL for the query you
might see what I have dont wrong.. I have only added the
function to the this field so far..
Thanks
Raj
SELECT masterdata.project_name, CCur(CInt(Sum
([mrc_revenue]))) AS [Current MRR], Sum([mrc_revenue]*12)
AS [Annualized MRR], Sum(masterdata.nrc_revenue) AS
[Current NRR], Sum(masterdata.nrc_revenue) AS [Annualized
NRR], Sum(masterdata.supportDI) AS [Current DI], Sum
(masterdata.supportDI) AS [Annualized DI], [current mrr]+
[current nrr]+[current di] AS [Total Current],
[annualized mrr]+[annualized nrr]+[annualized di] AS
[Total Annualized], Sum(masterdata.mrc_billing_stopped)
AS Credits, Count(masterdata.fc_handoff) AS [# of
Circuits], Sum(masterdata.fc_costsavings) AS [Total
Dollars]
FROM masterdata
WHERE (((masterdata.date_resolved) Is Not Null))
GROUP BY masterdata.project_name
HAVING (((masterdata.project_name) Not Like "Facility
Cost - Network Alarms" And (masterdata.project_name) Not
Like "Retail Dead Accounts"));

-----Original Message-----
Sorry - looks like there were too many ( characters in the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Ken,
I am having a problem with this function. When i run the
query I display #Error for the fields that has cents that
should be rounded..
Thanks
-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Use the Round function as a wrapper around the Sum function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

I have a query that sums dollars amounts and what
Click to expand...
i
would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do
Click to expand...
this
is a
query..
Thank you




.
Click to expand...
Click to expand...


.
Click to expand...
 
Can Sum([mrc_revenue]) be a Null value?

Try this expression:

CCur(CInt(Nz(Sum([FieldName]),0)))

--

Ken Snell
<MS ACCESS MVP>

Raj said:
Ken,
I am not sure what is happening but I am still get the
#Error in the field.. Below is the SQL for the query you
might see what I have dont wrong.. I have only added the
function to the this field so far..
Thanks
Raj
SELECT masterdata.project_name, CCur(CInt(Sum
([mrc_revenue]))) AS [Current MRR], Sum([mrc_revenue]*12)
AS [Annualized MRR], Sum(masterdata.nrc_revenue) AS
[Current NRR], Sum(masterdata.nrc_revenue) AS [Annualized
NRR], Sum(masterdata.supportDI) AS [Current DI], Sum
(masterdata.supportDI) AS [Annualized DI], [current mrr]+
[current nrr]+[current di] AS [Total Current],
[annualized mrr]+[annualized nrr]+[annualized di] AS
[Total Annualized], Sum(masterdata.mrc_billing_stopped)
AS Credits, Count(masterdata.fc_handoff) AS [# of
Circuits], Sum(masterdata.fc_costsavings) AS [Total
Dollars]
FROM masterdata
WHERE (((masterdata.date_resolved) Is Not Null))
GROUP BY masterdata.project_name
HAVING (((masterdata.project_name) Not Like "Facility
Cost - Network Alarms" And (masterdata.project_name) Not
Like "Retail Dead Accounts"));

-----Original Message-----
Sorry - looks like there were too many ( characters in the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Ken,
I am having a problem with this function. When i run the
query I display #Error for the fields that has cents that
should be rounded..
Thanks

-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

"Ken Snell [MVP]" <[email protected]>
wrote in message
Use the Round function as a wrapper around the Sum
function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

message
I have a query that sums dollars amounts and what i
would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this
is a
query..
Thank you




.
Click to expand...


.
Click to expand...
Click to expand...
 
Ken,
Yes, it can have null value. I am still getting the
error. I will use the first expression you suggested, it
work, I just lose my $, the user will have to live with
it..lol
Thanks for all the help
Raj
-----Original Message-----
Can Sum([mrc_revenue]) be a Null value?

Try this expression:

CCur(CInt(Nz(Sum([FieldName]),0)))

--

Ken Snell
<MS ACCESS MVP>

Ken,
I am not sure what is happening but I am still get the
#Error in the field.. Below is the SQL for the query you
might see what I have dont wrong.. I have only added the
function to the this field so far..
Thanks
Raj
SELECT masterdata.project_name, CCur(CInt(Sum
([mrc_revenue]))) AS [Current MRR], Sum([mrc_revenue] *12)
AS [Annualized MRR], Sum(masterdata.nrc_revenue) AS
[Current NRR], Sum(masterdata.nrc_revenue) AS [Annualized
NRR], Sum(masterdata.supportDI) AS [Current DI], Sum
(masterdata.supportDI) AS [Annualized DI], [current mrr]+
[current nrr]+[current di] AS [Total Current],
[annualized mrr]+[annualized nrr]+[annualized di] AS
[Total Annualized], Sum(masterdata.mrc_billing_stopped)
AS Credits, Count(masterdata.fc_handoff) AS [# of
Circuits], Sum(masterdata.fc_costsavings) AS [Total
Dollars]
FROM masterdata
WHERE (((masterdata.date_resolved) Is Not Null))
GROUP BY masterdata.project_name
HAVING (((masterdata.project_name) Not Like "Facility
Cost - Network Alarms" And (masterdata.project_name) Not
Like "Retail Dead Accounts"));

-----Original Message-----
Sorry - looks like there were too many ( characters in the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

Ken,
I am having a problem with this function. When i
Click to expand...
run
the
query I display #Error for the fields that has cents that
should be rounded..
Thanks

-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

"Ken Snell [MVP]"
Click to expand...
wrote in message
Use the Round function as a wrapper around the Sum
function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

message
I have a query that sums dollars amounts and
Click to expand...
what
i
would
like to do is have the results round to the nearest
dollars. Can someone please tell me how to do this
is a
query..
Thank you




.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Strange error. See what this expression does:

CCur(Round(Sum([FieldName]), 0))


--

Ken Snell
<MS ACCESS MVP>

Raj said:
Ken,
Yes, it can have null value. I am still getting the
error. I will use the first expression you suggested, it
work, I just lose my $, the user will have to live with
it..lol
Thanks for all the help
Raj
-----Original Message-----
Can Sum([mrc_revenue]) be a Null value?

Try this expression:

CCur(CInt(Nz(Sum([FieldName]),0)))

--

Ken Snell
<MS ACCESS MVP>

Ken,
I am not sure what is happening but I am still get the
#Error in the field.. Below is the SQL for the query you
might see what I have dont wrong.. I have only added the
function to the this field so far..
Thanks
Raj
SELECT masterdata.project_name, CCur(CInt(Sum
([mrc_revenue]))) AS [Current MRR], Sum([mrc_revenue] *12)
AS [Annualized MRR], Sum(masterdata.nrc_revenue) AS
[Current NRR], Sum(masterdata.nrc_revenue) AS [Annualized
NRR], Sum(masterdata.supportDI) AS [Current DI], Sum
(masterdata.supportDI) AS [Annualized DI], [current mrr]+
[current nrr]+[current di] AS [Total Current],
[annualized mrr]+[annualized nrr]+[annualized di] AS
[Total Annualized], Sum(masterdata.mrc_billing_stopped)
AS Credits, Count(masterdata.fc_handoff) AS [# of
Circuits], Sum(masterdata.fc_costsavings) AS [Total
Dollars]
FROM masterdata
WHERE (((masterdata.date_resolved) Is Not Null))
GROUP BY masterdata.project_name
HAVING (((masterdata.project_name) Not Like "Facility
Cost - Network Alarms" And (masterdata.project_name) Not
Like "Retail Dead Accounts"));


-----Original Message-----
Sorry - looks like there were too many ( characters in
the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

message
Ken,
I am having a problem with this function. When i run
the
query I display #Error for the fields that has cents
that
should be rounded..
Thanks

-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

"Ken Snell [MVP]"
Click to expand...
wrote in message
Use the Round function as a wrapper around the Sum
function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

message
I have a query that sums dollars amounts and what
i
would
like to do is have the results round to the
nearest
dollars. Can someone please tell me how to do
this
is a
query..
Thank you




.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Ken,
It works!!
Thank you!!!!
Raj
-----Original Message-----
Strange error. See what this expression does:

CCur(Round(Sum([FieldName]), 0))


--

Ken Snell
<MS ACCESS MVP>

Ken,
Yes, it can have null value. I am still getting the
error. I will use the first expression you suggested, it
work, I just lose my $, the user will have to live with
it..lol
Thanks for all the help
Raj
-----Original Message-----
Can Sum([mrc_revenue]) be a Null value?

Try this expression:

CCur(CInt(Nz(Sum([FieldName]),0)))

--

Ken Snell
<MS ACCESS MVP>

Ken,
I am not sure what is happening but I am still get the
#Error in the field.. Below is the SQL for the query you
might see what I have dont wrong.. I have only added the
function to the this field so far..
Thanks
Raj
SELECT masterdata.project_name, CCur(CInt(Sum
([mrc_revenue]))) AS [Current MRR], Sum
Click to expand...
([mrc_revenue]
*12)
AS [Annualized MRR], Sum(masterdata.nrc_revenue) AS
[Current NRR], Sum(masterdata.nrc_revenue) AS [Annualized
NRR], Sum(masterdata.supportDI) AS [Current DI], Sum
(masterdata.supportDI) AS [Annualized DI], [current mrr]+
[current nrr]+[current di] AS [Total Current],
[annualized mrr]+[annualized nrr]+[annualized di] AS
[Total Annualized], Sum (masterdata.mrc_billing_stopped)
AS Credits, Count(masterdata.fc_handoff) AS [# of
Circuits], Sum(masterdata.fc_costsavings) AS [Total
Dollars]
FROM masterdata
WHERE (((masterdata.date_resolved) Is Not Null))
GROUP BY masterdata.project_name
HAVING (((masterdata.project_name) Not Like "Facility
Cost - Network Alarms" And (masterdata.project_name) Not
Like "Retail Dead Accounts"));


-----Original Message-----
Sorry - looks like there were too many ( characters in
the expression:

CCur(CInt(Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

message
Ken,
I am having a problem with this function. When i run
the
query I display #Error for the fields that has cents
that
should be rounded..
Thanks

-----Original Message-----
Alternatively, you can use the CInt function:

CCur(CInt((Sum([FieldName])))


--

Ken Snell
<MS ACCESS MVP>

"Ken Snell [MVP]"
Click to expand...
wrote in message
Use the Round function as a wrapper around the Sum
function:

Round(Sum([FieldName]), 0)


--

Ken Snell
<MS ACCESS MVP>

message
I have a query that sums dollars amounts and what
i
would
like to do is have the results round to the
nearest
dollars. Can someone please tell me how to do
this
is a
query..
Thank you




.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top