Help formatting currency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID) Like
::CanID::));

I want to format the result so that it includes a $ sign and commas. I think
I need a convert statement, but I can't figure out where to put it.

Any help much appreciated.

Doug
 
Do the following at the point where you want to display it on your page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug
 
You would have to change "recordsetname" to the name of the recordset your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

HopelesslyLost said:
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug

Thomas A. Rowe said:
Do the following at the point where you want to display it on your page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
Click to expand...
Click to expand...
 
Yes, I did that.

Thomas A. Rowe said:
You would have to change "recordsetname" to the name of the recordset your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

HopelesslyLost said:
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug

Thomas A. Rowe said:
Do the following at the point where you want to display it on your page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID) Like
::CanID::));

I want to format the result so that it includes a $ sign and commas. I think
I need a convert statement, but I can't figure out where to put it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Sorry - I see now that you are using SQL Server - my solution works for
Access.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
FrontPage Support: http://www.frontpagemvps.com/




HopelesslyLost said:
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug

Thomas A. Rowe said:
Do the following at the point where you want to display it on your page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
Click to expand...
Click to expand...
 
Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS MoneyValue


--
Cheers,
Jon
Microsoft MVP

HopelesslyLost said:
Yes, I did that.

Thomas A. Rowe said:
You would have to change "recordsetname" to the name of the recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and commas.
I think
I need a convert statement, but I can't figure out where to put it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Jon,

That isn't working for me either. Would it make a difference how the Amount
variable is stored? The Amount field is stored as 'money'.

Jon Spivey said:
Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS MoneyValue


--
Cheers,
Jon
Microsoft MVP

HopelesslyLost said:
Yes, I did that.

Thomas A. Rowe said:
You would have to change "recordsetname" to the name of the recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency') AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and commas.
I think
I need a convert statement, but I can't figure out where to put it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Hi,
How do you mean not working? What's going wrong? It wouldn't matter how the
field is stored all we're doing is converting to varchar and sticking a
dollar sign in front. The only thing that might effect is of you have money
amounts with more than 2 digits after the decimal, if that is/might be the
case you can do
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,1000000.23456),1)),1,100))
compare that output with
SELECT '$'+CONVERT(VARCHAR(100), 1000000.23456 , 1)

Sub your money field for 1000000.23456


--
Cheers,
Jon
Microsoft MVP



HopelesslyLost said:
Jon,

That isn't working for me either. Would it make a difference how the
Amount
variable is stored? The Amount field is stored as 'money'.

Jon Spivey said:
Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS
MoneyValue


--
Cheers,
Jon
Microsoft MVP

message
Yes, I did that.

:

You would have to change "recordsetname" to the name of the recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency')
AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right
place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

in
message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount
FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and
commas.
I think
I need a convert statement, but I can't figure out where to put
it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
I used your suggestion to solve part of my problem.

My page contains two database results wizard tables, one lists amounts. Here
is the SQL:

SELECT *, '$'+CONVERT(varchar(12), Amount, 1) AS Amount
FROM GovContrib WHERE (CanID = '::CanID::')
ORDER BY ContribDate DESC


The other Sums the amounts, but I can't seem to get the syntax right to use
the CONVERT. Here's the SQL now, which returns the correct answer but not the
currency formatting:


SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID) Like
::CanID::));

Where does the Convert statement that works in the first go in the second?


Jon Spivey said:
Hi,
How do you mean not working? What's going wrong? It wouldn't matter how the
field is stored all we're doing is converting to varchar and sticking a
dollar sign in front. The only thing that might effect is of you have money
amounts with more than 2 digits after the decimal, if that is/might be the
case you can do
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,1000000.23456),1)),1,100))
compare that output with
SELECT '$'+CONVERT(VARCHAR(100), 1000000.23456 , 1)

Sub your money field for 1000000.23456


--
Cheers,
Jon
Microsoft MVP



HopelesslyLost said:
Jon,

That isn't working for me either. Would it make a difference how the
Amount
variable is stored? The Amount field is stored as 'money'.

Jon Spivey said:
Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS
MoneyValue


--
Cheers,
Jon
Microsoft MVP

message
Yes, I did that.

:

You would have to change "recordsetname" to the name of the recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the query:

SELECT GovContrib.CanID, Format(Sum([GovContrib.Amount]),'Currency')
AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right
place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

in
message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount
FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and
commas.
I think
I need a convert statement, but I can't figure out where to put
it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Hi,
Wrap the convert around the sum
SELECT GovContrib.CanID, '$' + Convert(varchar(100),Sum(GovContrib.Amount),
1) AS SumOfAmount

--
Cheers,
Jon
Microsoft MVP

HopelesslyLost said:
I used your suggestion to solve part of my problem.

My page contains two database results wizard tables, one lists amounts.
Here
is the SQL:

SELECT *, '$'+CONVERT(varchar(12), Amount, 1) AS Amount
FROM GovContrib WHERE (CanID = '::CanID::')
ORDER BY ContribDate DESC


The other Sums the amounts, but I can't seem to get the syntax right to
use
the CONVERT. Here's the SQL now, which returns the correct answer but not
the
currency formatting:


SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID) Like
::CanID::));

Where does the Convert statement that works in the first go in the second?


Jon Spivey said:
Hi,
How do you mean not working? What's going wrong? It wouldn't matter how
the
field is stored all we're doing is converting to varchar and sticking a
dollar sign in front. The only thing that might effect is of you have
money
amounts with more than 2 digits after the decimal, if that is/might be
the
case you can do
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,1000000.23456),1)),1,100))
compare that output with
SELECT '$'+CONVERT(VARCHAR(100), 1000000.23456 , 1)

Sub your money field for 1000000.23456


--
Cheers,
Jon
Microsoft MVP



message
Jon,

That isn't working for me either. Would it make a difference how the
Amount
variable is stored? The Amount field is stored as 'money'.

:

Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS
MoneyValue


--
Cheers,
Jon
Microsoft MVP

message
Yes, I did that.

:

You would have to change "recordsetname" to the name of the
recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

in
message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the
query:

SELECT GovContrib.CanID,
Format(Sum([GovContrib.Amount]),'Currency')
AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right
place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on
your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

"HopelesslyLost" <[email protected]>
wrote
in
message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount
FROM
GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and
commas.
I think
I need a convert statement, but I can't figure out where to
put
it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Jon,

That worked. Thanks so much.

Doug

Jon Spivey said:
Hi,
Wrap the convert around the sum
SELECT GovContrib.CanID, '$' + Convert(varchar(100),Sum(GovContrib.Amount),
1) AS SumOfAmount

--
Cheers,
Jon
Microsoft MVP

HopelesslyLost said:
I used your suggestion to solve part of my problem.

My page contains two database results wizard tables, one lists amounts.
Here
is the SQL:

SELECT *, '$'+CONVERT(varchar(12), Amount, 1) AS Amount
FROM GovContrib WHERE (CanID = '::CanID::')
ORDER BY ContribDate DESC


The other Sums the amounts, but I can't seem to get the syntax right to
use
the CONVERT. Here's the SQL now, which returns the correct answer but not
the
currency formatting:


SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount FROM
GovContrib GROUP BY GovContrib.CanID HAVING (((GovContrib.CanID) Like
::CanID::));

Where does the Convert statement that works in the first go in the second?


Jon Spivey said:
Hi,
How do you mean not working? What's going wrong? It wouldn't matter how
the
field is stored all we're doing is converting to varchar and sticking a
dollar sign in front. The only thing that might effect is of you have
money
amounts with more than 2 digits after the decimal, if that is/might be
the
case you can do
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,1000000.23456),1)),1,100))
compare that output with
SELECT '$'+CONVERT(VARCHAR(100), 1000000.23456 , 1)

Sub your money field for 1000000.23456


--
Cheers,
Jon
Microsoft MVP



message
Jon,

That isn't working for me either. Would it make a difference how the
Amount
variable is stored? The Amount field is stored as 'money'.

:

Hi,
like this
SELECT '$'+CONVERT(VARCHAR(100), Sum(GovContrib.Amount) , 1) AS
MoneyValue


--
Cheers,
Jon
Microsoft MVP

message
Yes, I did that.

:

You would have to change "recordsetname" to the name of the
recordset
your select statement is part
of, for my suggestion to work.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

in
message
Thanks for the help, but I'm still having trouble.

Kathleen's suggestion returns an error message:

Server error: Unable to retrieve schema information from the
query:

SELECT GovContrib.CanID,
Format(Sum([GovContrib.Amount]),'Currency')
AS
SumOfAmount FROM GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID) Like 1));

[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a
recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


I guess I'm not inserting Thomas' suggested code in the right
place(s),
because nothing changes no matter where I put it.

Doug

:

Do the following at the point where you want to display it on
your
page:

<%=FormatCurrency(recordsetname("fieldname"))%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

"HopelesslyLost" <[email protected]>
wrote
in
message
I'm using this SQL statement to sum a column called Amount:

SELECT GovContrib.CanID, Sum(GovContrib.Amount) AS SumOfAmount
FROM
GovContrib GROUP BY GovContrib.CanID HAVING
(((GovContrib.CanID)
Like
::CanID::));

I want to format the result so that it includes a $ sign and
commas.
I think
I need a convert statement, but I can't figure out where to
put
it.

Any help much appreciated.

Doug
Click to expand...
Click to expand...
Click to expand...
 
Back
Top