Display dollar value in words

Is this possible in RS? I want to display $1000 as one thousand dollars and
xx/100.
Thanks for any help.Here are a pair of SQL functions you can deploy on the SQL Server side
(or adapt to your needs).
The first one puts numbers into words - this is used by the second
function that spells out dollar amounts.
CREATE FUNCTION dbo.Number99ToWord (@.amount TINYINT)
RETURNS VARCHAR(20)
AS
BEGIN --FUNCTION
DECLARE @.result VARCHAR(20)
SET @.result = ''
IF @.amount = 0.00
SET @.result = 'No'
ELSE
IF @.amount BETWEEN 11 AND 19
BEGIN
SET @.result = RTRIM(SUBSTRING(
'Eleven Twelve Thirteen Fourteen Fifteen Sixteen
SeventeenEighteen Nineteen',
((@.amount - 10) - 1) * 9 + 1, 9))
END --IF
ELSE
BEGIN
IF @.amount > 9
SET @.result = RTRIM(SUBSTRING(
'Ten Twenty Thirty Forty Fifty Sixty SeventyEighty
Ninety',
((@.amount / 10) - 1) * 7 + 1, 7))
IF @.amount % 10 <> 0
SET @.result = @.result + CASE WHEN @.result <> '' THEN '-' ELSE
'' END +
RTRIM(SUBSTRING('One Two ThreeFour Five Six
SevenEightNine',
((@.amount % 10) - 1) * 5 + 1, 5))
END --ELSE
RETURN @.result
END --FUNCTION
CREATE FUNCTION dbo.strSpellNumber (@.amount DECIMAL(24,12))
RETURNS VARCHAR(200)
AS
BEGIN --FUNCTION
DECLARE @.result VARCHAR(200)
DECLARE @.keywordSwi BIT
DECLARE @.cents TINYINT
DECLARE @.amt BIGINT
SET @.cents = (@.amount - FLOOR(@.amount)) * 100
set @.cents = FLOOR(@.cents)
SET @.amt = ROUND(@.amount, 0, -1)
SET @.result = ''
--check for less than $1
IF @.amt < 1.00
SELECT @.result = @.result + dbo.Number99ToWord(0) + ' '
--check for billions
IF @.amt > 999999999
BEGIN
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000000) +
' Billion '
SET @.amt = @.amt % 1000000000
END --IF
--check for hundreds of millions
SET @.keywordSwi = 0
IF @.amt > 99999999
BEGIN
SET @.keywordSwi = 1
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000000) + '
Hundred '
SET @.amt = @.amt % 100000000
END --IF
--check for millions
IF @.amt > 999999
BEGIN
SET @.keywordSwi = 1
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000)
SET @.amt = @.amt % 1000000
END --IF
IF @.keywordSwi > 0
BEGIN
SET @.result = @.result + ' Million '
SET @.keywordSwi = 0
END --IF
--check for hundreds of thousands
IF @.amt > 99999
BEGIN
SET @.keywordSwi = 1
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000) + '
Hundred '
SET @.amt = @.amt % 100000
END --IF
--check for thousands
IF @.amt > 999
BEGIN
SET @.keywordSwi = 1
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000)
SET @.amt = @.amt % 1000
END --IF
IF @.keywordSwi > 0
BEGIN
SET @.result = @.result + ' Thousand '
SET @.keywordSwi = 0
END --IF
--check for hundreds
IF @.amt > 99
BEGIN
SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100) + '
Hundred '
SET @.amt = @.amt % 100
END --IF
--check for other dollars
IF @.amt > 0.99
BEGIN
SELECT @.result = @.result + dbo.Number99ToWord(@.amt) + ' '
END --IF
IF @.result = 'One'
SET @.result = @.result + 'Dollar '
ELSE
SET @.result = @.result + 'Dollars '
SET @.result = @.result + 'and '
IF dbo.Number99ToWord(@.cents) = 'One'
SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cent'
ELSE
SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cents'
RETURN @.result
END --FUNCTION|||Great thanks...now how, where do I place the functions? Thanks for your help
:)
"Parker" wrote:
> Here are a pair of SQL functions you can deploy on the SQL Server side
> (or adapt to your needs).
> The first one puts numbers into words - this is used by the second
> function that spells out dollar amounts.
>
> CREATE FUNCTION dbo.Number99ToWord (@.amount TINYINT)
> RETURNS VARCHAR(20)
> AS
> BEGIN --FUNCTION
> DECLARE @.result VARCHAR(20)
> SET @.result = ''
> IF @.amount = 0.00
> SET @.result = 'No'
> ELSE
> IF @.amount BETWEEN 11 AND 19
> BEGIN
> SET @.result = RTRIM(SUBSTRING(
> 'Eleven Twelve Thirteen Fourteen Fifteen Sixteen
> SeventeenEighteen Nineteen',
> ((@.amount - 10) - 1) * 9 + 1, 9))
> END --IF
> ELSE
> BEGIN
> IF @.amount > 9
> SET @.result = RTRIM(SUBSTRING(
> 'Ten Twenty Thirty Forty Fifty Sixty SeventyEighty
> Ninety',
> ((@.amount / 10) - 1) * 7 + 1, 7))
> IF @.amount % 10 <> 0
> SET @.result = @.result + CASE WHEN @.result <> '' THEN '-' ELSE
> '' END +
> RTRIM(SUBSTRING('One Two ThreeFour Five Six
> SevenEightNine',
> ((@.amount % 10) - 1) * 5 + 1, 5))
> END --ELSE
> RETURN @.result
> END --FUNCTION
>
> CREATE FUNCTION dbo.strSpellNumber (@.amount DECIMAL(24,12))
> RETURNS VARCHAR(200)
> AS
> BEGIN --FUNCTION
> DECLARE @.result VARCHAR(200)
> DECLARE @.keywordSwi BIT
> DECLARE @.cents TINYINT
> DECLARE @.amt BIGINT
> SET @.cents = (@.amount - FLOOR(@.amount)) * 100
> set @.cents = FLOOR(@.cents)
> SET @.amt = ROUND(@.amount, 0, -1)
> SET @.result = ''
> --check for less than $1
> IF @.amt < 1.00
> SELECT @.result = @.result + dbo.Number99ToWord(0) + ' '
> --check for billions
> IF @.amt > 999999999
> BEGIN
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000000) +
> ' Billion '
> SET @.amt = @.amt % 1000000000
> END --IF
> --check for hundreds of millions
> SET @.keywordSwi = 0
> IF @.amt > 99999999
> BEGIN
> SET @.keywordSwi = 1
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000000) + '
> Hundred '
> SET @.amt = @.amt % 100000000
> END --IF
> --check for millions
> IF @.amt > 999999
> BEGIN
> SET @.keywordSwi = 1
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000)
> SET @.amt = @.amt % 1000000
> END --IF
> IF @.keywordSwi > 0
> BEGIN
> SET @.result = @.result + ' Million '
> SET @.keywordSwi = 0
> END --IF
> --check for hundreds of thousands
> IF @.amt > 99999
> BEGIN
> SET @.keywordSwi = 1
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000) + '
> Hundred '
> SET @.amt = @.amt % 100000
> END --IF
> --check for thousands
> IF @.amt > 999
> BEGIN
> SET @.keywordSwi = 1
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000)
> SET @.amt = @.amt % 1000
> END --IF
> IF @.keywordSwi > 0
> BEGIN
> SET @.result = @.result + ' Thousand '
> SET @.keywordSwi = 0
> END --IF
> --check for hundreds
> IF @.amt > 99
> BEGIN
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100) + '
> Hundred '
> SET @.amt = @.amt % 100
> END --IF
> --check for other dollars
> IF @.amt > 0.99
> BEGIN
> SELECT @.result = @.result + dbo.Number99ToWord(@.amt) + ' '
> END --IF
> IF @.result = 'One'
> SET @.result = @.result + 'Dollar '
> ELSE
> SET @.result = @.result + 'Dollars '
> SET @.result = @.result + 'and '
> IF dbo.Number99ToWord(@.cents) = 'One'
> SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cent'
> ELSE
> SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cents'
> RETURN @.result
> END --FUNCTION
>|||I finally figured it out and for those of you who may need it in the future
see below.
I pasted the following code in the Code section of RS and then called it via
a text box by using Code.NAMEOFFUNCTION($0.00)
Private Function SetOnes(ByVal Number As Integer) As String
Dim OnesArray(9) As String
OnesArray(1) = "One"
OnesArray(2) = "Two"
OnesArray(3) = "Three"
OnesArray(4) = "Four"
OnesArray(5) = "Five"
OnesArray(6) = "Six"
OnesArray(7) = "Seven"
OnesArray(8) = "Eight"
OnesArray(9) = "Nine"
SetOnes = OnesArray(Number)
End Function
Private Function SetTens(ByVal Number As Integer) As String
Dim TensArray(9) As String
TensArray(1) = "Ten"
TensArray(2) = "Twenty"
TensArray(3) = "Thirty"
TensArray(4) = "Fourty"
TensArray(5) = "Fifty"
TensArray(6) = "Sixty"
TensArray(7) = "Seventy"
TensArray(8) = "Eighty"
TensArray(9) = "Ninety"
Dim TeensArray(9) As String
TeensArray(1) = "Eleven"
TeensArray(2) = "Twelve"
TeensArray(3) = "Thirteen"
TeensArray(4) = "Fourteen"
TeensArray(5) = "Fifteen"
TeensArray(6) = "Sixteen"
TeensArray(7) = "Seventeen"
TeensArray(8) = "Eighteen"
TeensArray(9) = "Nineteen"
Dim tmpInt1 As Integer
Dim tmpInt2 As Integer
Dim tmpString As String
tmpInt1 = Int(Number / 10)
tmpInt2 = Number Mod 10
tmpString = TensArray(tmpInt1)
If (tmpInt1 = 1 And tmpInt2 > 0) Then
tmpString = TeensArray(tmpInt2)
Else
If (tmpInt1 > 1 And tmpInt2 > 0) Then
tmpString = tmpString + " " + SetOnes(tmpInt2)
End If
End If
SetTens = tmpString
End Function
Private Function SetHundreds(ByVal Number As Integer) As String
Dim tmpInt1 As Integer
Dim tmpInt2 As Integer
Dim tmpString As String
tmpInt1 = Int(Number / 100)
tmpInt2 = Number Mod 100
If tmpInt1 > 0 Then tmpString = SetOnes(tmpInt1) + " Hundred"
If tmpInt2 > 0 Then
If tmpString > "" Then tmpString = tmpString + " "
If tmpInt2 < 10 Then tmpString = tmpString + SetOnes(tmpInt2)
If tmpInt2 > 9 Then tmpString = tmpString + SetTens(tmpInt2)
End If
SetHundreds = tmpString
End Function
Private Function SetThousands(ByVal Number As Long) As String
Dim tmpInt1 As Integer
Dim tmpInt2 As Integer
Dim tmpString As String
tmpInt1 = Int(Number / 1000)
tmpInt2 = Number - (tmpInt1 * 1000)
If tmpInt1 > 0 Then tmpString = SetHundreds(tmpInt1) + " Thousand"
If tmpInt2 > 0 Then
If tmpString > "" Then tmpString = tmpString + " "
tmpString = tmpString + SetHundreds(tmpInt2)
End If
SetThousands = tmpString
End Function
HTH someone else :)
"DigitalVixen" wrote:
> Great thanks...now how, where do I place the functions? Thanks for your help
> :)
> "Parker" wrote:
> > Here are a pair of SQL functions you can deploy on the SQL Server side
> > (or adapt to your needs).
> >
> > The first one puts numbers into words - this is used by the second
> > function that spells out dollar amounts.
> >
> >
> > CREATE FUNCTION dbo.Number99ToWord (@.amount TINYINT)
> > RETURNS VARCHAR(20)
> > AS
> > BEGIN --FUNCTION
> > DECLARE @.result VARCHAR(20)
> > SET @.result = ''
> > IF @.amount = 0.00
> > SET @.result = 'No'
> > ELSE
> > IF @.amount BETWEEN 11 AND 19
> > BEGIN
> > SET @.result = RTRIM(SUBSTRING(
> > 'Eleven Twelve Thirteen Fourteen Fifteen Sixteen
> > SeventeenEighteen Nineteen',
> > ((@.amount - 10) - 1) * 9 + 1, 9))
> > END --IF
> > ELSE
> > BEGIN
> > IF @.amount > 9
> > SET @.result = RTRIM(SUBSTRING(
> > 'Ten Twenty Thirty Forty Fifty Sixty SeventyEighty
> > Ninety',
> > ((@.amount / 10) - 1) * 7 + 1, 7))
> > IF @.amount % 10 <> 0
> > SET @.result = @.result + CASE WHEN @.result <> '' THEN '-' ELSE
> > '' END +
> > RTRIM(SUBSTRING('One Two ThreeFour Five Six
> > SevenEightNine',
> > ((@.amount % 10) - 1) * 5 + 1, 5))
> > END --ELSE
> > RETURN @.result
> > END --FUNCTION
> >
> >
> > CREATE FUNCTION dbo.strSpellNumber (@.amount DECIMAL(24,12))
> > RETURNS VARCHAR(200)
> > AS
> > BEGIN --FUNCTION
> > DECLARE @.result VARCHAR(200)
> > DECLARE @.keywordSwi BIT
> > DECLARE @.cents TINYINT
> > DECLARE @.amt BIGINT
> > SET @.cents = (@.amount - FLOOR(@.amount)) * 100
> > set @.cents = FLOOR(@.cents)
> > SET @.amt = ROUND(@.amount, 0, -1)
> > SET @.result = ''
> > --check for less than $1
> > IF @.amt < 1.00
> > SELECT @.result = @.result + dbo.Number99ToWord(0) + ' '
> > --check for billions
> > IF @.amt > 999999999
> > BEGIN
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000000) +
> > ' Billion '
> > SET @.amt = @.amt % 1000000000
> > END --IF
> > --check for hundreds of millions
> > SET @.keywordSwi = 0
> > IF @.amt > 99999999
> > BEGIN
> > SET @.keywordSwi = 1
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000000) + '
> > Hundred '
> > SET @.amt = @.amt % 100000000
> > END --IF
> > --check for millions
> > IF @.amt > 999999
> > BEGIN
> > SET @.keywordSwi = 1
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000000)
> > SET @.amt = @.amt % 1000000
> > END --IF
> > IF @.keywordSwi > 0
> > BEGIN
> > SET @.result = @.result + ' Million '
> > SET @.keywordSwi = 0
> > END --IF
> > --check for hundreds of thousands
> > IF @.amt > 99999
> > BEGIN
> > SET @.keywordSwi = 1
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100000) + '
> > Hundred '
> > SET @.amt = @.amt % 100000
> > END --IF
> > --check for thousands
> > IF @.amt > 999
> > BEGIN
> > SET @.keywordSwi = 1
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 1000)
> > SET @.amt = @.amt % 1000
> > END --IF
> > IF @.keywordSwi > 0
> > BEGIN
> > SET @.result = @.result + ' Thousand '
> > SET @.keywordSwi = 0
> > END --IF
> > --check for hundreds
> > IF @.amt > 99
> > BEGIN
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt / 100) + '
> > Hundred '
> > SET @.amt = @.amt % 100
> > END --IF
> > --check for other dollars
> > IF @.amt > 0.99
> > BEGIN
> > SELECT @.result = @.result + dbo.Number99ToWord(@.amt) + ' '
> > END --IF
> > IF @.result = 'One'
> > SET @.result = @.result + 'Dollar '
> > ELSE
> > SET @.result = @.result + 'Dollars '
> > SET @.result = @.result + 'and '
> > IF dbo.Number99ToWord(@.cents) = 'One'
> > SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cent'
> > ELSE
> > SELECT @.result = @.result + dbo.Number99ToWord(@.cents) + ' Cents'
> > RETURN @.result
> > END --FUNCTION
> >
> >