Formatting help, please

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

Guest

I have found directions for formatting a field as currency, but I'm uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to format as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
 
SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::') ORDER
BY ContributionDate DESC


--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
 
Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?
 
Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




HopelesslyLost said:
Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

Kathleen Anderson said:
SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
Click to expand...
Click to expand...
 
Thanks, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

Kathleen Anderson said:
Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




HopelesslyLost said:
Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

Kathleen Anderson said:
SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
Click to expand...
Click to expand...
Click to expand...
 
If you only want the dollar sign add
'$' +
in front of (ContributionAmount)
SELECT *, '$' + (ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

If you need (ContributionAmount) formated with two decimal places, try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount,
FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

HopelesslyLost said:
Thanks, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

Kathleen Anderson said:
Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




HopelesslyLost said:
Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

:

SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the suggestion. I need the decimal places, so I tried the second
one and got this error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'FROM'.


Dan L said:
If you only want the dollar sign add
'$' +
in front of (ContributionAmount)
SELECT *, '$' + (ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

If you need (ContributionAmount) formated with two decimal places, try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount,
FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

HopelesslyLost said:
Thanks, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

Kathleen Anderson said:
Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

:

SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I think it's the comma after ContributionAmount and before FROM. Try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::') ORDER BY
ContributionDate DESC

HopelesslyLost said:
Thanks for the suggestion. I need the decimal places, so I tried the second
one and got this error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'FROM'.


Dan L said:
If you only want the dollar sign add
'$' +
in front of (ContributionAmount)
SELECT *, '$' + (ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

If you need (ContributionAmount) formated with two decimal places, try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount,
FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

HopelesslyLost said:
Thanks, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

:

Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

:

SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
Click to expand...
Click to expand...
Click to expand...
 
That works, Dan. Thanks very much.

Doug

Dan L said:
Sorry, I think it's the comma after ContributionAmount and before FROM. Try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::') ORDER BY
ContributionDate DESC

HopelesslyLost said:
Thanks for the suggestion. I need the decimal places, so I tried the second
one and got this error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'FROM'.


Dan L said:
If you only want the dollar sign add
'$' +
in front of (ContributionAmount)
SELECT *, '$' + (ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

If you need (ContributionAmount) formated with two decimal places, try

SELECT *, CONVERT(varchar(12), ContributionAmount, 1) AS ContributionAmount,
FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC

:

Thanks, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

:

Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

:

SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

Doug
Click to expand...
Click to expand...
Click to expand...
 
Format the output when you display it on the page, using

<%=FormatCurrency(Recordset("ContributionAmount"),2)%>

--
==============================================
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, but that didn't work either. Got the same error. I'm digging through
SQL Server books now, it looks like maybe the CONVERT function is the answer.
Anybody know?

Kathleen Anderson said:
Try this:

SELECT *, FormatCurrency(ContributionAmount) FROM BCC WHERE (CanID =
'::CanID::' AND Industry = '::Industry::') ORDER BY ContributionDate DESC


--

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




HopelesslyLost said:
Kathleen, thanks for helping -- again.

Problem, though. SQL Server doesn't like the Format command:

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

Any ideas?

:

SELECT *, Format(ContributionAmount,'$#,##0.00') as newContributionAmount
FROM BCC WHERE (CanID = '::CanID::' AND Industry = '::Industry::')
ORDER
BY ContributionDate DESC


--

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




message
I have found directions for formatting a field as currency, but I'm
uncertain
about the placement of the FORMAT statement.

Here's the SQL from the DBRW:

SELECT * FROM BCC WHERE (CanID = '::CanID::' AND Industry =
'::Industry::') ORDER BY ContributionDate DESC

My table contains a field called ContributionAmount that I want to
format
as
currency. Where would that fit into my SQL statement?

Thanks,

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