Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access Numbers 1
- Thread starter Illreap33
- Start date
TonyJollans
Programmer
Hi Illreap33,
It's not easy in Excel or Access and there's nothing built in which will do it for you. It needs code; I could write some for you but I have a vague memory of having seen some posted somewhere in TT (you could try searching - or someone else might know where it is). If you turn up a blank, come back.
Enjoy,
Tony
It's not easy in Excel or Access and there's nothing built in which will do it for you. It needs code; I could write some for you but I have a vague memory of having seen some posted somewhere in TT (you could try searching - or someone else might know where it is). If you turn up a blank, come back.
Enjoy,
Tony
PeterMoran
MIS
Hi Illreap33 and Tony,
For what it's worth here is an Excel function which does more or less what you want in Excel. I don't know how it would operate in Access, but it would probably not have to be changed too much.
Function DollarsToText(amount)
' DollarsToText created 26/4/00 PJMoran
' Converts Amounts to Text (ex Ins Ms Excel Oct 97)
' with some additional mods to Australianise it
'
If amount >= 1000000 Or amount < 0 Then
DollarsToText = "***VOID***"
Exit Function
End If
hth = Int(amount / 100000)
tth = Int((Int(amount) Mod 100000) / 10000)
th = Int((Int(amount) Mod 10000) / 1000)
h = Int((Int(amount) Mod 1000) / 100)
t = Int((Int(amount) Mod 100) / 10)
o = Int((Int(amount) * 100 Mod 1000) / 100)
C = Application.Round(amount - Int(amount), 2) * 100
array1 = Array("One ", "Two ", "Three ", "Four ", "Five ", "Six ", _
"Seven ", "Eight ", "Nine "
array2 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", _
"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen "
array3 = Array("", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", _
"Seventy ", "Eighty ", "Ninety "
If hth = 0 Then
part1 = ""
Else
part1 = array1(hth) & "Hundred "
End If
If tth = 0 And th < 1 Then
part1and = ""
Else: part1and = "and "
End If
If amount < 100000 Then
part1and = ""
End If
If tth = 0 Then
part2 = ""
ElseIf tth = 1 Then
part2 = array2(tth + th)
Else
part2 = array3(tth)
End If
If th = 0 Or tth = 1 Then
part3 = ""
Else
part3 = array1(th)
End If
If hth = 0 And tth = 0 And th = 0 Then
part4 = ""
Else
part4 = "Thousand "
End If
If h = 0 Then
part5 = ""
Else
part5 = array1(h) & "Hundred "
End If
If t = 0 And o < 1 Then
partand = ""
Else: partand = "and "
End If
If amount < 100 Then
partand = ""
End If
If t = 0 Then
part6 = ""
ElseIf t = 1 Then
part6 = array2(t + o)
Else
part6 = array3(t)
End If
If amount < 1 Then
part7 = "No "
ElseIf o = 0 Or t = 1 Then
part7 = ""
Else
part7 = array1(o)
End If
DollarsToText = part1 & part1and & part2 & part3 & part4 & part5 & partand & _
part6 & part7 & "Dollar(s) and " & C & " cents"
End Function
At least this may be a starting point!
Good Luck!
Peter Moran
Two heads are always better than one!
For what it's worth here is an Excel function which does more or less what you want in Excel. I don't know how it would operate in Access, but it would probably not have to be changed too much.
Function DollarsToText(amount)
' DollarsToText created 26/4/00 PJMoran
' Converts Amounts to Text (ex Ins Ms Excel Oct 97)
' with some additional mods to Australianise it
'
If amount >= 1000000 Or amount < 0 Then
DollarsToText = "***VOID***"
Exit Function
End If
hth = Int(amount / 100000)
tth = Int((Int(amount) Mod 100000) / 10000)
th = Int((Int(amount) Mod 10000) / 1000)
h = Int((Int(amount) Mod 1000) / 100)
t = Int((Int(amount) Mod 100) / 10)
o = Int((Int(amount) * 100 Mod 1000) / 100)
C = Application.Round(amount - Int(amount), 2) * 100
array1 = Array("One ", "Two ", "Three ", "Four ", "Five ", "Six ", _
"Seven ", "Eight ", "Nine "
array2 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", _
"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen "
array3 = Array("", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", _
"Seventy ", "Eighty ", "Ninety "
If hth = 0 Then
part1 = ""
Else
part1 = array1(hth) & "Hundred "
End If
If tth = 0 And th < 1 Then
part1and = ""
Else: part1and = "and "
End If
If amount < 100000 Then
part1and = ""
End If
If tth = 0 Then
part2 = ""
ElseIf tth = 1 Then
part2 = array2(tth + th)
Else
part2 = array3(tth)
End If
If th = 0 Or tth = 1 Then
part3 = ""
Else
part3 = array1(th)
End If
If hth = 0 And tth = 0 And th = 0 Then
part4 = ""
Else
part4 = "Thousand "
End If
If h = 0 Then
part5 = ""
Else
part5 = array1(h) & "Hundred "
End If
If t = 0 And o < 1 Then
partand = ""
Else: partand = "and "
End If
If amount < 100 Then
partand = ""
End If
If t = 0 Then
part6 = ""
ElseIf t = 1 Then
part6 = array2(t + o)
Else
part6 = array3(t)
End If
If amount < 1 Then
part7 = "No "
ElseIf o = 0 Or t = 1 Then
part7 = ""
Else
part7 = array1(o)
End If
DollarsToText = part1 & part1and & part2 & part3 & part4 & part5 & partand & _
part6 & part7 & "Dollar(s) and " & C & " cents"
End Function
At least this may be a starting point!
Good Luck!
Peter Moran
Two heads are always better than one!
-
1
- Thread starter
- #4
PeterMoran
MIS
Hi Illreap33,
As you may see from the top of the code I have had it in use for several years so it was just a matter of giving you a copy. This is a popular question in Excel. I hope you can use it in Access nearly as easily.
Regards,
Peter Moran
Two heads are always better than one!
As you may see from the top of the code I have had it in use for several years so it was just a matter of giving you a copy. This is a popular question in Excel. I hope you can use it in Access nearly as easily.
Regards,
Peter Moran
Two heads are always better than one!
This is from Microsoft.com, it looks similar to above but I havn't checked. Hope it helps.
ACC2000: How to Convert Currency or Numbers into English Words
Applies To
This article was previously published under Q210586
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56:
One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
To create the ConvertCurrencyToEnglish() function, follow these steps:
Create a new module and type the following line in the Declarations section if the line is not already there:Option Explicit
Type the following four procedures:Function ConvertCurrencyToEnglish (ByVal MyNumber)
Dim Temp
Dim Dollars, Cents
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, "."
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert cents
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Cents = ConvertTens(Temp)
' Strip off cents from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English dollars.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
' Clean up dollars.
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
' Clean up cents.
Select Case Cents
Case ""
Cents = " And No Cents"
Case "One"
Cents = " And One Cent"
Case Else
Cents = " And " & Cents & " Cents"
End Select
ConvertCurrencyToEnglish = Dollars & Cents
End Function
Private Function ConvertHundreds (ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens (ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit (ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
To test this function, type the following line in the Immediate window, and then press ENTER:? ConvertCurrencyToEnglish(1234.56)
How to Use the ConvertCurrencyToEnglish() Function on a Form
To demonstrate the use of the ConvertCurrencyToEnglish function on a form, follow these steps:
Open the sample database Northwind.mdb.
Open the Orders form in Design view and add a text box to the form. Set the text box's ControlSource property as follows: =ConvertCurrencyToEnglish([Total])
Open the Orders form in Form view. Note that the text box displays an English word representation of the total order amount computed in the form's Total field.
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com
ACC2000: How to Convert Currency or Numbers into English Words
Applies To
This article was previously published under Q210586
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56:
One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
To create the ConvertCurrencyToEnglish() function, follow these steps:
Create a new module and type the following line in the Declarations section if the line is not already there:Option Explicit
Type the following four procedures:Function ConvertCurrencyToEnglish (ByVal MyNumber)
Dim Temp
Dim Dollars, Cents
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, "."
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert cents
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Cents = ConvertTens(Temp)
' Strip off cents from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English dollars.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
' Clean up dollars.
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
' Clean up cents.
Select Case Cents
Case ""
Cents = " And No Cents"
Case "One"
Cents = " And One Cent"
Case Else
Cents = " And " & Cents & " Cents"
End Select
ConvertCurrencyToEnglish = Dollars & Cents
End Function
Private Function ConvertHundreds (ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens (ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit (ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
To test this function, type the following line in the Immediate window, and then press ENTER:? ConvertCurrencyToEnglish(1234.56)
How to Use the ConvertCurrencyToEnglish() Function on a Form
To demonstrate the use of the ConvertCurrencyToEnglish function on a form, follow these steps:
Open the sample database Northwind.mdb.
Open the Orders form in Design view and add a text box to the form. Set the text box's ControlSource property as follows: =ConvertCurrencyToEnglish([Total])
Open the Orders form in Form view. Note that the text box displays an English word representation of the total order amount computed in the form's Total field.
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question
