Number to Words | Microsoft Access Sample Code

Function for Outputing Numbers - Currency As Words

Microsoft Access Function: Convert Numbers to Currency in Words

When working with Microsoft Access, there are scenarios where you need to display numeric values as text. This is particularly useful in applications such as checks or invoices, where monetary values need to appear in words. For example, instead of displaying "$123.45," the text "One Hundred Twenty-Three Dollars and Forty-Five Cents" is required. Using a VBA function, you can easily achieve this transformation and enhance your database functionality.

Below is a VBA function, wsiSpellNumber, that converts numeric values into text. This powerful tool is designed for situations where you need to output numbers in English words on forms or reports. It works seamlessly with currency or general numeric values and can be integrated directly into your Microsoft Access application.

Main Function: wsiSpellNumber

Public Function wsiSpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
         Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = " and No Cents"
        Case "One"
            Cents = " and One Cent"
        Case Else
            Cents = " and " & Cents & " Cents"
    End Select
    wsiSpellNumber = Dollars & Cents
End Function

Supporting Functions

• GetHundreds: Converts numbers from 100-999 into text format.
• GetTens: Converts numbers from 10-99 into text, including special cases for teens.
• GetDigit: Handles single-digit numbers (1-9) and outputs the corresponding text value.

These supporting functions work together with wsiSpellNumber to ensure accurate and comprehensive text conversion for any numeric input.

Use Case: Microsoft Access Number to Words Database

The Microsoft Access Number to Words Database is a sample template that demonstrates various implementations of the wsiSpellNumber function. You can use this template to better understand how to integrate the function into your database projects. It is particularly useful for applications such as:

• Generating checks with numeric values displayed in text format.
• Creating invoices or receipts where currency needs to be spelled out.
• Any reports or forms that require numeric values in textual format.

If this sample database does not fully meet your needs, our team at Winning Solutions, Inc. (WSI) can help you customize it to fit your specific requirements. Simply contact us for a quote, and we’ll work with you to deliver a solution tailored to your exact needs.

Main Menu

Form Criteria

Employer

Form Criteria - Output

Main Menu Form

Main Menu - Modern Look (Access 2010/2013)

Employer

Form Criteria - Output - Modern Look (Access 2010/2013)

Previous Next

Live Demonstration: To experience a more interactive and personalized demonstration of this WSI template, we invite you to contact WSI and schedule a live demonstration with one of our team members. This opportunity allows you to engage directly with us, either as an alternative to or in conjunction with downloading and exploring the demo version of the template at your leisure. Please note that downloading Microsoft Office files, including Microsoft Access database files, might trigger a security warning on your computer. To avoid this and get a comprehensive overview, you have the option of viewing the template through a virtual meeting on Teams or Zoom with a WSI representative.
Once you purchase the database template, you will receive the full version of the database in a standard Access Database file. The purchased database will have full access to the database window, all design objects, the visual basic project code, and you will be able to modify or add to the design in any way you wish (this does not apply to the run-time/compiled versions). If you lack the time to integrate this database into your system, please Contact Us and we will be glad to help with your integration process. If you like the basics of what you see in the database, but need enhancements to make the system functional for your business, please Contact Us and we will develop a quote for you to implement the additional features and enhancements that you require.

$9.99 - this allows you to use this database template however you wish within your organization.

$19.99 - for unlimited developer use - this allows you to use this database template however you wish for any applications you develop and distribute.