Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Word Field Formatting
Field format switch \DollarText returns error message when data in field
exceeds 999999.99. How do I fix the switch to accept date of 1 million or more. |
#2
|
|||
|
|||
You cannot do that without a macro. Here's one
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 " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") If DecimalPlace 0 Then Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2) Cents = ConvertTens(Temp) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber "" 'convert last 3 digits to English Dollars Temp = ConvertHundreds(Right(MyNumber, 3)) If Temp "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) 3 Then 'remove last 3 comverted digits MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop 'clean up dollars Select Case Dollars Case "" Dollars = "NoDollars" 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 If Val(MyNumber) = 0 Then Exit Function 'append leading zeros to number MyNumber = Right("000" & MyNumber, 3) 'do we have hundreds place digit to convert? If Left(MyNumber, 1) "0" Then Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred " End If 'do we have 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 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 ================================================== ========= Sub TestAboveFunction() Dim MyNumber MyNumber = 551521896.32 MsgBox ConvertCurrencyToEnglish(ByVal MyNumber) End Sub ================================================== ========= -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Victor Lazlo" wrote in message news Field format switch \DollarText returns error message when data in field exceeds 999999.99. How do I fix the switch to accept date of 1 million or more. |
#3
|
|||
|
|||
You can try
SET x { MERGEFIELD mylargenumber } }{ SET r { =MOD(x,1000000) } }{ SET m { =INT((x-r)/1000000) } }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \*Dollartext } but it will not give you "billions", only "thousand millions" and you may need to do something more for the "cents". Peter Jamieson "Victor Lazlo" wrote in message news Field format switch \DollarText returns error message when data in field exceeds 999999.99. How do I fix the switch to accept date of 1 million or more. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word2000 letterhead merge | Mailmerge | |||
is word perfect compatible with office word? | Microsoft Word Help | |||
1st word in heading flush left second word flush right | New Users | |||
Boiletplates from Word Perfect | Microsoft Word Help | |||
How do I create & merge specific data base & master documents? | New Users |