How To Write Number In Words Using MS Access Visual Basic Application

Example is like this: 8,630.15 will be spelt "Eight thousand six hundred thirty dollars and fifteen cents" (see below sample picture).

Numbers in English are associated with the singular and plural of a word. Using Microsoft Access-Visual Basic Application, we can write numbers in English words just like words for amounts written in a formal document such as invoice and check (cheque).





Copy and paste the following script or syntax to your Microsoft Access module (please visit http://www.varieart.com/article62-How-To-Write-Number-In-English-Words-Using-MS-Access-Visual-Basic-Application to download the full code example):

Function ReadNumberEnglish(ByRef intNumber As Integer) As String 'Number definition
Dim strNumber As String
strNumber = ""
Select Case intNumber
Case 0
strNumber = ""
Case 1
strNumber = "one"
Case 2
strNumber = "two"
Case 3
strNumber = "three"
Case 4
strNumber = "four"
Case 5
strNumber = "five"
Case 6
strNumber = "six"
Case 7
strNumber = "seven"
Case 8
strNumber = "eight"
Case 9
strNumber = "nine"
Case 10
strNumber = "ten"
Case 11
strNumber = "eleven"
Case 12
strNumber = "twelve"
Case 13
strNumber = "thirteen"
Case 14
strNumber = "fourteen"
Case 15
strNumber = "fifteen"
Case 16
strNumber = "sixteen"
Case 17
strNumber = "seventeen"
Case 18
strNumber = "eighteen"
Case 19
strNumber = "nineteen"
Case 20
strNumber = "twenty"
Case 30
strNumber = "thirty"
Case 40
strNumber = "forty"
Case 50
strNumber = "fifty"
Case 60
strNumber = "sixty"
Case 70
strNumber = "seventy"
Case 80
strNumber = "eighty"
Case 90
strNumber = "ninety"
End Select
ReadNumberEnglish = strNumber & " "
End Function


Function EnglishSaying(ByRef dbAmount As Double) As String
Dim intLen, intMod, intCounter, intCount, intAmount2, intCent As Integer
Dim intAmount1 As Double
Dim strSaid1, strSaid2, strSaid3, strSaid4, strSaid5, strAmountEnglish, strPart, _
strCent As String
If dbAmount = 0 Then
EnglishSaying = " "
Exit Function
End If
intAmount1 = Round(dbAmount, 2)
intCent = CInt(Round(dbAmount - Int(intAmount1), 2) * 100)
If dbAmount < 1 Then If intCent = 1 Then strCent = SayingBelowOneThousand(intCent) & "cent" Else strCent = SayingBelowOneThousand(intCent) & "cents" End If Else If intCent = 1 Then strCent = " dollars and " & SayingBelowOneThousand(intCent) & "cent" Else strCent = " dollars and " & SayingBelowOneThousand(intCent) & "cents" End If End If If intCent = 0 Then strCent = " dollars " If dbAmount < 2 And dbAmount >= 1 Then
If intCent = 1 Then
strCent = " dollar and " & SayingBelowOneThousand(intCent) & "cent"
Else
strCent = " dollar and " & SayingBelowOneThousand(intCent) & "cents"
End If
End If
strAmountEnglish = CStr(Int(intAmount1))
If Len(strAmountEnglish) >= 12 Then
MsgBox "Maximum amount you can enter is 99,999,999,999", vbCritical
Exit Function
End If
intLen = Len(strAmountEnglish)
intMod = intLen Mod 3
intCount = (intLen - intMod) / 3
intAmount2 = Val(Left(strAmountEnglish, intMod)) '23
strSaid1 = SayingBelowOneThousand(Val(intAmount2))


If intLen > 3 Then
For intCounter = 1 To intCount
strPart = Left(Right(strAmountEnglish, 3 * intCounter), 3)


If intCounter = 1 Then
strSaid4 = Trim(SayingBelowOneThousand(Val(strPart)))
If intCent = 0 Then strSaid4 = Trim(SayingBelowOneThousand(Val(strPart)))
If intCent > 0 Then strSaid4 = Trim(SayingBelowOneThousand(Val(strPart)))
If Val(strPart) = 0 Then strSaid4 = ""
strSaid3 = strSaid4 & strCent
strSaid5 = Trim(strSaid3)
End If


If intCounter = 2 Then
strSaid4 = Trim(SayingBelowOneThousand(Val(strPart)))
If strSaid5 = "" Then strSaid4 = strSaid4
strSaid3 = strSaid4 & " thousand " & Trim(strSaid3)
If Val(strPart) = 0 Then strSaid3 = strSaid5
strSaid5 = Trim(strSaid3)
End If


If intCounter = 3 Then
strSaid4 = Trim(SayingBelowOneThousand(Val(strPart)))
If strSaid5 = "" Then strSaid4 = strSaid4
strSaid3 = strSaid4 & " million " & Trim(strSaid3)
If Val(strPart) = 0 Then strSaid3 = strSaid5
End If


Next intCounter
End If


If intLen > 3 And intLen < 6 Then strSaid1 = strSaid1 & "thousand " If intLen > 6 And intLen < 9 Then strSaid1 = strSaid1 & "million " If intLen > 9 Then strSaid1 = strSaid1 & "billion "
strSaid2 = Trim(strSaid1 & strSaid3)
If intLen <= 3 Then strSaid2 = SayingBelowOneHundred(Val(strAmountEnglish)) & _
Trim(strCent)
If intLen = 3 Then strSaid2 = SayingBelowOneThousand(Val(strAmountEnglish)) & _
Trim(strCent)
If Left(strSaid2, 4) = "and " Then strSaid2 = Mid(strSaid2, 5, Len(strSaid2) - 4)
strSaid2 = UCase(Left(strSaid2, 1)) & Mid(strSaid2, 2, Len(strSaid2))
If Right(strSaid2, 9) = " " Then strSaid2 = Left(strSaid2, Len(strSaid2) - 9) & " "
EnglishSaying = Trim(strSaid2)
End Function
Function SayingBelowOneHundred(ByRef dbSayingHundredLower As Integer) As String
'Function to say number below one hundred

After pasting the above syntax, build a Form consists of two text boxes, one for the input and the other for the output - calling EnglishSaying(variable) function.