Sum field not formatting as currency

[Kathy Webster]

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the header is
displaying as 245.

Whazzup?
TIA,
Kathy

 

[Duane Hookom]

Set the Format property of the text box to Currency.

 

[Kathy Webster]

It is. The format of the text box in the detail as well as the header are
both set to currency.

Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the header
is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Duane Hookom]

If the format property is set to currency and it is not showing currency
then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP

It is. The format of the text box in the detail as well as the header are
both set to currency.

Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the header
is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Kathy Webster]

Excellent. Beautiful. Thank you.

If the format property is set to currency and it is not showing currency
then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP

It is. The format of the text box in the detail as well as the header are
both set to currency.

Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the header
is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Kathy Webster]

Well, I thought it was beautiful, but when it Outputs to Excel, it shows
back up as a number with no decimals and no dollar sign....

If the format property is set to currency and it is not showing currency
then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP

It is. The format of the text box in the detail as well as the header are
both set to currency.

Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the header
is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Duane Hookom]

See if this sample solution from A.D. Tejpal meets your needs
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Reports_AccessToExcelAndWord.mdb'.

--
Duane Hookom
MS Access MVP

Well, I thought it was beautiful, but when it Outputs to Excel, it shows
back up as a number with no decimals and no dollar sign....

If the format property is set to currency and it is not showing currency
then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP

It is. The format of the text box in the detail as well as the header
are both set to currency.


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the
header is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Kathy Webster]

Well, simple is best. I'm sure there must be a way rather than having to
introduce Word into the picture, just for this one issue of the format of a
sum number.

See if this sample solution from A.D. Tejpal meets your needs
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Reports_AccessToExcelAndWord.mdb'.

--
Duane Hookom
MS Access MVP

Well, I thought it was beautiful, but when it Outputs to Excel, it shows
back up as a number with no decimals and no dollar sign....

If the format property is set to currency and it is not showing currency
then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP


It is. The format of the text box in the detail as well as the header
are both set to currency.


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in the
header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the
header is displaying as 245.

Whazzup?
TIA,
Kathy

 

[Duane Hookom]

I don't push reports to Excel and if I did, I would probably format the
cells afterward.

--
Duane Hookom
MS Access MVP

Well, simple is best. I'm sure there must be a way rather than having to
introduce Word into the picture, just for this one issue of the format of
a sum number.

See if this sample solution from A.D. Tejpal meets your needs
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Reports_AccessToExcelAndWord.mdb'.

--
Duane Hookom
MS Access MVP

Well, I thought it was beautiful, but when it Outputs to Excel, it shows
back up as a number with no decimals and no dollar sign....

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
If the format property is set to currency and it is not showing
currency then try set the control source to:
=Val(sum([Payment]))


--
Duane Hookom
MS Access MVP


It is. The format of the text box in the detail as well as the header
are both set to currency.


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
Set the Format property of the text box to Currency.

--
Duane Hookom
MS Access MVP

This should be so simple. I can't figure out what I'm doing wrong.
I have a currency field in a report's details. I am summing it in
the header:
=sum([Payment])
The format of both is currency.
The details are displaying as currency ($220.00, $25.00) but the
header is displaying as 245.

Whazzup?
TIA,
Kathy

 

[onedaywhen]

If the format property is set to currency and it is not showing
currency then try set the control source to:
=Val(sum([Payment]))
Well, I thought it was beautiful, but when it Outputs to Excel, it shows
back up as a number with no decimals and no dollar sign....

I don't push reports to Excel and if I did, I would probably format the
cells afterward.

The the data is currency, why coerce it to double float using the VAL()
function?

SELECT TYPENAME(VAL(CCUR(245)))

returns 'Double'.

Jamie.

--

 

[Duane Hookom]

My expectation was the OP was doing something in the query (which we
couldn't see) to change the value to a variant. This was based on the fact
that the OP suggested setting the Format property to currency did not work.
I couldn't confirm this since it wasn't my database. Apparently the Format
property worked when using Val() so I must have been close with my
assumptions.

--
Duane Hookom
MS Access MVP

If the format property is set to currency and it is not showing
currency then try set the control source to:
=Val(sum([Payment]))
Well, I thought it was beautiful, but when it Outputs to Excel, it
shows
back up as a number with no decimals and no dollar sign....

I don't push reports to Excel and if I did, I would probably format the
cells afterward.

The the data is currency, why coerce it to double float using the VAL()
function?

SELECT TYPENAME(VAL(CCUR(245)))

returns 'Double'.

Jamie.

 

[Kathy Webster]

Hi 1day:
Where would I put this SELECT TYPENAME(VAL(CCUR(245)))?
My text box control source in the report is currently set to =Sum([Net
Amount]).
Again, it prints fine in Access, its only when I push it out to Excel that
it loses its formatting.
I don't want the users to have (forget) to format the Excel report after the
fact.

If the format property is set to currency and it is not showing
currency then try set the control source to:
=Val(sum([Payment]))
Well, I thought it was beautiful, but when it Outputs to Excel, it
shows
back up as a number with no decimals and no dollar sign....

I don't push reports to Excel and if I did, I would probably format the
cells afterward.

The the data is currency, why coerce it to double float using the VAL()
function?

SELECT TYPENAME(VAL(CCUR(245)))

returns 'Double'.

Jamie.

 

[onedaywhen]

Where would I put this SELECT TYPENAME(VAL(CCUR(245)))?

The TYPENAME function returns the data type of the column's value; it's
only useful for debugging purposes.

There is some doubt about the data type of your column. Run this query:

SELECT TYPENAME(SUM([Net Amount])) FROM T1;

replacing the T1 with the query/table source for the report, and post
the result here.

TIA,
Jamie.

--

 

[onedaywhen]

My expectation was the OP was doing something in the query (which we
couldn't see) to change the value to a variant.

Do you have an example of a query that returns a value as a Variant?

TIA,
Jamie.

--

 

[Duane Hookom]

A query in Northwind like:
SELECT ProductID, ProductName, UnitPrice,
Nz([UnitPrice]) AS LeftAlignedNz,
Format([UnitPrice],"Currency") AS LeftAlignedCurrency
FROM Products;
Will return the last two columns left aligned. My thought was these were a
form of variant since they display left aligned.

 

[onedaywhen]

A query in Northwind like:
SELECT ProductID, ProductName, UnitPrice,
Nz([UnitPrice]) AS LeftAlignedNz,
Format([UnitPrice],"Currency") AS LeftAlignedCurrency
FROM Products;
Will return the last two columns left aligned. My thought was these were a
form of variant since they display left aligned.

I think your example left aligns because it returns text e.g.

SELECT TYPENAME(FORMAT(CCUR(NOW()), 'Currency'))

returns 'String'.

In the OP's case, there can't be a currency symbol prefix e.g.

SELECT VAL(FORMAT(CCUR(NOW()), 'Currency'))

returns zero.

FWIW a value stored would normally SUM correctly e.g.

SELECT SUM('1,234.56')
FROM Customers;

and that VAL() works may indicate a suffix e.g.

SELECT SUM(VAL('1234.56 USD'))
FROM Customers;

Jamie.

--

 

[Kathy Webster]

It says Null.

Where would I put this SELECT TYPENAME(VAL(CCUR(245)))?

The TYPENAME function returns the data type of the column's value; it's
only useful for debugging purposes.

There is some doubt about the data type of your column. Run this query:

SELECT TYPENAME(SUM([Net Amount])) FROM T1;

replacing the T1 with the query/table source for the report, and post
the result here.

TIA,
Jamie.