feedburner
Enter your email address:

Delivered by FeedBurner

feedburner count

Convert a number to words

Here is a function that converts a given numeric value to words in dollars or other currency.  You can use this for your check writing application where you need to put the dollar amount in words in your checks.

For example:

? InWords(1234567890123.45)

should return:

One Trillion Two Hundred Thirty Four Billion Five Hundred Sixty Seven Million Eight Hundred Ninety Thousand One Hundred Twenty Three And 45/100 Dollars Only


Here’s the code:


Public Function InWords(varAmount As Variant) As String
  Dim strInWords As String
  Dim decAmount As Variant
  Dim intHundreds As Integer
  Dim intDecimal As Integer
  Dim strThou(5) As String
  Dim ctr As Byte
  
  ' Value should be less than 10 Trillion
  If varAmount > 9999999999999.99@ Then
    InWords = "*** Value too large to convert ***"
    Exit Function
  Else
    decAmount = CDec(varAmount)
  End If
  strThou(1) = ""
  strThou(2) = " Thousand "
  strThou(3) = " Million "
  strThou(4) = " Billion "
  strThou(5) = " Trillion "
  
  intDecimal = CInt((decAmount - Int(decAmount)) * 100)
  decAmount = Int(decAmount)
  For ctr = 1 To 5
    intHundreds = ((decAmount / 1000) - Int(decAmount / 1000)) * 1000
    If intHundreds > 0 Then
      strInWords = Hundreds(intHundreds) & strThou(ctr) & strInWords
    End If
    If decAmount >= 1 Then
      decAmount = Int(decAmount / 1000)
    Else
      Exit For
  End If
  Next ctr
 
  InWords = strInWords & " And " & intDecimal & "/100 Dollars Only"
 
End Function

 
Public Function Hundreds(intAmount As Integer) As String
  Static varOnes As Variant
  Static varTens As Variant
  Dim strWords As String
  Dim bOnes As Byte
  Dim bTens As Byte
  Dim bTeens As Byte
  Dim bHundreds As Byte
 
  If intAmount > 999 Then
    Hundreds = "***Parameter should be less than 999***"
    Exit Function
  End If
  If IsEmpty(varOnes) Then
    varOnes = Array("", "One", "Two", "Three", "Four", "Five", _
    "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", _
    "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _
    "Eighteen", "Nineteen")
  End If
 
  If IsEmpty(varTens) Then
    varTens = Array("", "Ten", "Twenty", "Thirty", "Forty", _
    "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
  End If
 
  bHundreds = Int(intAmount / 100)
  If bHundreds > 0 Then
    strWords = varOnes(bHundreds) & " Hundred "
  End If
  bTeens = ((intAmount / 100) - Int(intAmount / 100)) * 100
  If bTeens < 20 Then
    strWords = strWords & varOnes(bTeens)
  Else
    bTens = Int(intAmount / 10) - (Int(intAmount / 100) * 10)
    If bTens > 0 Then
      strWords = strWords & varTens(bTens) & " "
    End If
    bOnes = intAmount - (Int(intAmount / 10) * 10)
    If bOnes > 0 Then
      strWords = strWords & varOnes(bOnes)
    End If
  End If
  Hundreds = strWords
End Function


Read More...


Allow User to Add Values in Combo Box

It is often good practice to provide users with a list of values that they can assign to a field when entering data into a Microsoft Access database form. With the use of pre-filled lists like a combo box, you can prevent the user from making incorrect data entry, and minimize the chance of spelling mistakes or incorrect choice of values.

On some occasions, however, you may wish to allow the user to add new values to the underlying table.  I do this for a field like City as part of a contact address where I would give the user the ability to define a new City that is not on the original list.




This case would call for you to make use of the ComboBox’s NotInList event. To make it work, make sure that the following properties are set:

  • The ComboBox must have it's RowSource set to either a table or a query (not value list or field list)
  • You must set the Limit To List property to Yes (This event will only be triggered if this property is set)


Here’s the sample code for the NotInList event:



Private Sub cbxSYAddressCityID_NotInList( _
    NewData As String, Response As Integer)
    Dim    strMsg As String
    Dim    strSQL As String
    ' Exit if the combo box was cleared
    ' Delete this line if the field is required
    If Len(NewData) = 0 Then Exit Sub
    ' Confirm that the user wants to add the new city
    strMsg = """" & NewData & """" & " is not in the list." _
    & vbCr & vbCr & "Do you want to add it?"
    If MsgBox(strMsg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add the city, set the
        ' Response argument to suppress an error message
        ' and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        cbxSYAddressCityID.SetFocus
    Else
        ' If the user chose to add the new city, insert
        ' it the SYCity table
        strSQL = "INSERT INTO SYCity (SYCityName)" _
          &     " VALUES (" & """" & NewData & """" & ");"
        ' SetWarnings to False so the Dialog to confirm
        ' new record won't appear
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        ' SetWarnings to True again
        DoCmd.SetWarnings True
        ' Set Response argument to indicate that new data
        ' is being added.
        Response = acDataErrAdded
    End If
End Sub

Read More...


Opening a Dialog Form When Printing a Report

Before printing a report, you normally would want to enter report parameters in a dialog box such as the following:



The dialog form will popup when you open the Payroll Register report shown below:


To do this, we just need to put a code in the Open event of the report to open the dialog form as follows:

Private Sub Report_Open(Cancel As Integer)
  DoCmd.OpenForm "frmReportCriteria", acNormal, , , _
    acFormEdit, acDialog, "rptPayrollRegisterPerPeriod"
  If Not IsLoaded("frmReportCriteria") Then
    Cancel = True
  End If
End Sub



In the Close event of the report, we’ll need to close the form:


Private Sub Report_Close()
  Forms![frmReportCriteria].Visible = True
  DoCmd.Close acForm, "frmReportCriteria"
End Sub


In the Print button of the form, we can enter the following code:


Private Sub btnPrint_Click()
On Error GoTo Err_btnPrint_Click

  Dim strDocName As String
  Dim strFilter As String

  ' OpenArgs contains the report name
  strDocName = Me.OpenArgs
  If fmeForEmployee = 1 Then  ' All employees
    strFilter = ""
  Else                        ' Or individual employees
    strFilter = "[EmployeeID] = " & EmployeeID & " AND "
  End If
  strFilter = strFilter & "[PayPeriodID] Between " _
    & FromPayPeriodID & " And " & ToPayPeriodID

  Reports.Item(strDocName).Filter = strFilter
  Reports.Item(strDocName).FilterOn = True
  Me.Visible = False

Exit_btnPrint_Click:
  Exit Sub

Err_btnPrint_Click:
  MsgBox Err.Description
  Resume Exit_btnPrint_Click
End Sub


The code gets the report name from the OpenArgs passed by the report when it opens the dialog form. It then set the Filter property of the report to show only the records we selected in the form.


Read More...


Convert Numbers into String with Ordinal Suffix

Labels:

The VBA code below allows you to represent a numeric value as its ordinal position in a set. The VBA function takes a numeric value and returns the number with the correct ordinal suffix as a string.
For example:

  • strOrdinal(1)
returns “1st”, and
  • strOrdinal(42)
returns “42nd”
The strOrdinal function uses the Mod operator to get the last two digits of the number. For all values between 11 and 19, apply “th” as the suffix. Otherwise, it again uses Mod operator to get the last digit of the number and apply the following rules:
  • Numbers that end in 1, use “st”
  • Numbers that end in 2, use "nd"
  • Numbers that end in 3, use "rd"
  • Otherwise, use "th"

Here’s the code for the function that converts numbers into a string with ordinal suffix:

Public Function strOrdinal(lngVal As Long) As String 
  Dim intDigit As Integer 
  Dim intTeen As Integer 
  Dim strSuffix As String 
  ' Check for the "teens" first 
  intTeen = lngVal Mod 100 
  If intTeen >= 11 And intTeen <= 19 Then  
    ' if teens, use "th" 
    strSuffix = "th" 
  Else 
    ' Get the last digit 
    intDigit = lngVal Mod 10 
    Select Case intDigit 
      Case 1 
        strSuffix = "st" 
      Case 2 
        strSuffix = "nd" 
      Case 3 
        strSuffix = "rd"  
      Case Else 
        strSuffix = "th" 
      End Select 
    End If 
    strOrdinal = lngVal & strSuffix 
End Function

Read More...