Home

Data Entry and Decimal Numbers

 

Decimal Numeric Fields

 

A Field With Single-Precision Decimal Value

As we saw in Lesson 3, besides natural numbers, a database also supports decimal values. These are also referred with a level of precision. The most basic decimal number is said to have a single-precision.

To visually create a field that can hold small decimal numbers, you must display the table in Design View. After specifying its name, set its data type to Number. In the lower section of the window, click Field Size and select Single.

If you are creating a table using the Microsoft Access Object Library or DAO and you want a field that can handle regular decimal values without regards to precision, specify its data type as dbSingle or DB_SINGLE. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName
    
    Set fldWeeklyHours = tblEmployees.CreateField("WeeklyHours", dbSingle)
    tblEmployees.Fields.Append fldWeeklyHours

   ' Add the new table to the database.
   dbThisOne.TableDefs.Append tblEmployees
   dbThisOne.Close
End Sub

If you are creating the column using the SQL, specify its data type as SINGLE, FLOAT4, or IEEESINGLE.

Practical LearningPractical Learning: Introducing Built-In Functions

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Solas Property Management1 database
  3. In the Navigation Pane, right-click the NewInvoice form and click Design View

A Field With Double-Precision Decimal Value

One of the most popular categories of number used in most computer applications is the decimal number with double-precision.

When creating a table in Microsoft Access, to create a field that can hold very large decimal and precise numbers, display the table in the Design View. After specifying the field name, set its data type to Number. In the lower section of the window, click Field Size and select Double.

If you are programmatically creating a table using the Microsoft Access Object Library or DAO, to create a field that can hold decimal numbers with double-precision, specify its data type as dbDouble or DB_DOUBLE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating the table in SQL, specify the data type of the column as NUMBER, NUMERIC, DOUBLE, FLOAT8, or IEEEDOUBLE.

Practical LearningPractical Learning: Introducing Decimal Values

  1. On the form, right-click the Submit button and click Build Event...
  2. In the Choose Builder dialog box, double-click Code Builder
  3. Implement the event as follows:
     
    Private Sub cmdSubmit_Click()
        Dim dbInvoice As Object
        Dim tblInvoices As Object
        Dim fldInvoice As Object
        
        Set dbInvoice = CurrentDb
        
        DoCmd.RunSQL "CREATE TABLE Invoices(InvoiceID AUTOINCREMENT(1,1)," & _
                                           "InvoiceDate varchar(50))"
    
        Set tblInvoices = dbInvoice.TableDefs!Invoices
        
        Set fldInvoice = tblInvoices.CreateField("ContractorName", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("ContractorPhoneNumber", dbText, 20)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("ContractorAddress", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("ContractorCity", dbText, 40)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("ContractorState", dbText, 40)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("ContractorZIPCode", dbText, 16)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("LaborAddress", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("LaborCity", dbText, 50)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("LaborState", dbText, 50)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("LaborZIPCode", dbText, 20)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Labor1", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Labor2", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Labor3", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Labor4", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Labor5", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item1Name", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item1UnitPrice", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item1Quantity", dbText, dbInteger)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item1SubTotal", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item2Name", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item2UnitPrice", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item2Quantity", dbByte)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item2SubTotal", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item3Name", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item3UnitPrice", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item3Quantity", dbByte)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item3SubTotal", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item4Name", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item4UnitPrice", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item4Quantity", dbByte)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item4SubTotal", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item5Name", dbText, 80)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item5UnitPrice", dbDouble)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item5Quantity", dbByte)
        tblInvoices.Fields.Append fldInvoice
        Set fldInvoice = tblInvoices.CreateField("Item5SubTotal", dbDouble)
        tblInvoices.Fields.Append fldInvoice
    
        Rem dbInvoice.TableDefs.Append tblInvoices
        
        DoCmd.RunSQL "ALTER TABLE Invoices ADD COLUMN TotalLabor double;"
        DoCmd.RunSQL "ALTER TABLE Invoices ADD COLUMN TotalItems double;"
        DoCmd.RunSQL "ALTER TABLE Invoices ADD COLUMN Notes Memo;"
    
        MsgBox "A table named Invoices has been added to the database"
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. Click the Submit button
  6. Click OK to accept
  7. Close the form
  8. When asked whether you want to save, click No
  9. In the Navigation Pane, right-click the Invoices form and click Design View
  10. Access the form's Properties window and set its Record Source to Invoices.
    Switch the form to the Form View.
    If the controls on the form display errors, display the form to Design View. Click each control on the form and, using the Properties window, change the Control Source as follows:
     
    Field Name Control Source
    InvoiceID InvoiceID
    InvoiceDate InvoiceDate
    ContractorName ContractorName
    ContractorPhoneNumber ContractorPhoneNumber
    ContractorAddress ContractorAddress
    ContractorCity ContractorCity
    ContractorState ContractorState
    ContractorZIPCode ContractorZIPCode
    LaborAddress LaborAddress
    LaborCity LaborCity
    LaborState LaborState
    LaborZIPCode LaborZIPCode
    Labor1 Labor1
    Labor2 Labor2
    Labor3 Labor3
    Labor4 Labor4
    Labor5 Labor5
    Item1Name Item1Name
    Item1UnitPrice Item1UnitPrice
    Item1Quantity Item1Quantity
    Item1SubTotal Item1SubTotal
    Item2Name Item2Name
    Item2UnitPrice Item2UnitPrice
    Item2Quantity Item2Quantity
    Item2SubTotal Item2SubTotal
    Item3Name Item3Name
    Item3UnitPrice Item3UnitPrice
    Item3Quantity Item3Quantity
    Item3SubTotal Item3SubTotal
    Item4Name Item4Name
    Item4UnitPrice Item4UnitPrice
    Item4Quantity Item4Quantity
    Item4SubTotal Item4SubTotal
    Item5Name Item5Name
    Item5UnitPrice Item5UnitPrice
    Item5Quantity Item5Quantity
    Item5SubTotal Item5SubTotal
    TotalLabor TotalLabor
    TotalItems TotalItems
    Notes Notes
     
    Invoice
  11. Switch the Invoices form to Form View to preview
     
    Invoices
  12. Close the form
  13. When asked whether you want to save, click Yes

Data Entry on Decimal Fields

After creating a decimal field, you can use it as you see fit. For example, you and the user can perform data entry on the field. To specify the value of a number-based field, assign a natural or decimal number to it.

Currency Fields

 

Introduction

When creating a table in the Design View, if you want a column to hold monetary values, you can set its data type to a currency. If you are creating a column on a table, display it in Datasheet View then click a column header or a cell under it. In the Data Type & Formatting section of the Ribbon, click the arrow of the Data Type combo box and select Currency:

Currency

If you are creating the table in Design View, after specifying the column name, set its Data Type to Currency:

Currency

After setting this data type, when a person is using the application, the database would refer to the language set in the Control Panel and the rules in the Currency property page:

For example, if the database is being used in the US, the US dollar and its rules, including the $ symbol, would be applied. If you want to apply different rules to a particular column, after setting its Data Type to Currency, in the lower section of the window, click the arrow of the Field Size property and select the desired option:

Currency

Setting Currency in the Microsoft Access Object Library and DAO

If you are programmatically creating the column either in Microsoft Access Object Library or in DAO and you want it to hold monetary values, set its data type to dbCurrency or DB_CURRENCY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object
    Dim colHourlySalary As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
    tblEmployees.Fields.Append colHourlySalary
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Setting Currency in the SQL

If you are using SQL, specify its data type as Money or Currency. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "WeeklyHours Double, " & _
                 "HourlySalary Money);"
End Sub

Both Money and Currency have the same effect in Microsoft Access.

Data Entry on a Currency Field

Data entry on a currency-based field follows the same rules as for natural numbers and decimal number fields. To perform data entry in a database, the user can display a table in Datasheet View or a form in Form View. For a currency field, the user can click it and type a natural or a decimal number.

While the user can enter the currency symbol in a field, you cannot. If you are programmatically performing data entry on a currency-based field, assign only the value made of digits and possibly a decimal separator.

Built-In Numeric Functions

 

Introduction

In our introduction to procedures, we saw different ways or creating procedures and functions. Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them.

Checking Whether a Value is Numeric

One of the most valuable operations you will perform on a value consists of finding out whether it is numeric or not. To assist you with this, the Visual Basic language provides a function named IsNumeric. Its syntax is:

Public Function IsNumeric(ByVal Expression As Variant) As Boolean

This function takes as argument the value or expression to be evaluated. If the argument holds or can produce a valid integer or a decimal value, the function returns True. Here is an example:

Private Sub cmdFunction_Click()
    Dim Value As Variant

    Value = 258.08 * 9920.3479

    msgbox "Is Numeric? " & IsNumeric(Value)
End Sub 

If the argument is holding any other value that cannot be identified as a number, the function produces False. Here is an example:

Private Sub cmdFunction_Click()
    Dim Value As Variant

    Value = #12/4/1770#

    MsgBox "Is Numeric? " & IsNumeric(Value)
End Sub

This would produce:

IsNumeric

Specifying a Color

To assist you with specifying the color of anything, the Visual Basic language provides a function named RGB. Its syntax is:

Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long

This function takes three arguments and each must hold a value between 0 and 255. The first argument represents the ratio of red of the color. The second argument represents the green ratio of the color. The last argument represents the blue of the color. After the function has been called, it produces a number whose maximum value can be 255 * 255 * 255 = 16,581,375, which represents a color.

The Absolute Value

The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of 12 is 12.

To let you find the absolute value of a number, the Visual Basic language provides a function named Abs. Its syntax is:

Function Abs(number) As Number

This function takes one argument. The argument must be a number or an expression convertible to a number:

  • If the argument is a positive number, the function returns it
  • If the argument is zero, the function returns 0
  • If the argument is a negative number, the function is returns its equivalent positive value

Here is an example that retrieves the number in a text box named txtNumber, finds the absolute value of that number, and displays it in the same text box:

Private Sub cmdAbsoluteValue_Click()
    txtNumber = Abs(txtNumber)
End Sub

Getting the Integral Part of a Number

If you have a decimal number but are interested only in the integral part, to assist you with retrieving that part, the Visual Basic language provides the Int() and the Fix() functions. Their syntaxes are:

Function Int(ByVal Number As { Number | Expression } ) As Integer
Function Fix(ByVal Number As { Number | Expression } ) As Integer

Each function must take one argument. The value of the argument must be number-based. This means it can be an integer or a floating-point number. If the value of the argument is integer-based, the function returns the (whole) number. Here is an example

Private Sub cmdFunction_Click()
    Dim Number As Integer

    Number = 28635
    MsgBox Int(Number)
End Sub

This would produce:

Int

If the value of the argument is a decimal number, the function returns only the integral part. Here is an example

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 7942.225 * 202.46
    MsgBox Int(Number)
End Sub

This would produce:

Int

This function always returns the integral part only, even if you ask it to return a floating-point-based value. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Single

    Number = 286345.9924
    MsgBox Int(Number)
End Sub

This would produce:

Int

Expression Evaluation

To assist you with evaluating an expression, the Visual Basic language provides the Eval() function. Its syntax is:

Eval(Expression)

The argument is passed to this function as a string. The argument can be as simple as an arithmetic operation as in 12 * 11, which would be Eval("12*11") or it can be a complex expression. When the function receives the argument, it uses its own built-in mechanism to analyze it and find out the type of the value it should return. If the expression appears as a calculation, then the function would return a numeric value. Otherwise, the function may return a string.

Practical LearningPractical Learning: Using the Eval() Function

  1. In the Navigation Pane, right-click the NewInvoice form and click Design View
  2. On the form, double-click the text box under the Quantity label to access its Properties window
  3. In the Events tab of the Properties window, double-click On Lost Focus, then click its ellipses button and implement the event as follows:
     
    Private Sub Item1Quantity_LostFocus()
        [Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity])
    End Sub
  4. In the Object combo box, select Item2Quantity
  5. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub Item2Quantity_LostFocus()
        [Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity])
    End Sub
  6. In the Object combo box, select Item3Quantity
  7. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub Item3Quantity_LostFocus()
        [Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity])
    End Sub
  8. In the Object combo box, select Item4Quantity
  9. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub Item4Quantity_LostFocus()
        [Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity])
    End Sub
  10. In the Object combo box, select Item5Quantity
  11. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub Item5Quantity_LostFocus()
        [Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity])
    End Sub
  12. Return to the form and save it

The Numeric Value of an Expression

When an expression is supposed to produce a numeric value, it is important to make sure you get that value before involving it in another operation. Microsoft Office provides a function that can be used to easily get the numeric value of an expression. The function is called Val and its syntax is:

Val(Expression)

In reality, this function can be considered as two in one. In other words, it can produce either a natural or a real number. This function takes as argument either an unknown value or an expression, such as an algebraic calculation. In most cases, or whenever possible, you should be able to predict the type of expression passed as argument. For example, if you pass an algebraic operation that calculates the sum of two natural numbers, you should be able to predict that the function would return a natural number. In this case, you can retrieve the integer that the function returns. Here is an example:

Private Sub cmdValue_Click()
    Dim intValue%
    
    intValue% = Val(145 + 608)
    txtValue = intValue%
End Sub

In the same way, you can ask this function to perform an algebraic operation on two or more natural numbers, two or more decimal numbers, two or more numbers that include at least one decimal number. If the function receives an operation that involves two natural numbers, it would return a natural number. If the function receives an operation that involves at least one decimal number and one or more natural numbers, the function would return a decimal number. If the function receives an operation that involves decimal numbers, it would produce a decimal number.

Regardless of the types of numbers that this function receives, you still can impose the type of value you want to retrieve. If the function receives an operation that involves only natural numbers, you may prefer to get a decimal number from it. If the function receives an operation that involves at least one decimal number and one or more natural numbers, you can still retrieve only the natural number. Here is an example:

Private Sub cmdValue_Click()
    Dim intValue%
    
    intValue% = Val(455 + 1250.85 + 88)
    txtValue = intValue%
End Sub

This call of the Val() function would return a decimal number but you mange to retrieve the natural number.

Practical LearningPractical Learning: Using the Val() Function

  1. Return to Microsoft Visual Basic and change the LostFocus events as follows:
     
    Private Sub Item1Quantity_LostFocus()
        [Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity])
        [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                           Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                           Nz([Item5SubTotal]))
    End Sub
    
    Private Sub Item2Quantity_LostFocus()
        [Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity])
        [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                           Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                           Nz([Item5SubTotal]))
    End Sub
    
    Private Sub Item3Quantity_LostFocus()
        [Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity])
        [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                           Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                           Nz([Item5SubTotal]))
    End Sub
    
    Private Sub Item4Quantity_LostFocus()
        [Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity])
        [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                           Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                           Nz([Item5SubTotal]))
    End Sub
    
    Private Sub Item5Quantity_LostFocus()
        [Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity])
        [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                           Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                           Nz([Item5SubTotal]))
    End Sub
  2. Return to the form and switch it to Form View
  3. Save the form
  4. Create a few invoices
  5. Close the form
 

 

 
 

Statistical Functions

 

Introduction

Besides the Val() and the Eval() function that we saw earlier, Microsoft Access is equipped with functions used to perform routine arithmetic and algebraic operations. It also provides functions used in collections of values. Some functions can "scan" a series of records of a table or a form and provide the appropriate value.

Using Statistical Functions

In arithmetic, we are used to calculating the sum of a series of values. In a database, you can also calculate the sum of the values that a certain column holds. This operation can be carried by the Sum() function. Its syntax is:

Sum(FieldName)

This function takes one argument. The argument can be the name of a column. If it is, all the values held by that column, throughout the records of the table, would be summed up and this function would return the result.

The argument to this function can also be an expression. It can even be a combination of columns.

Besides the Sum() function, another arithmetic-oriented function you can use is Count() and it uses the same syntax as Sum(). The Count() function is used to count the number of entries of a column among the records of a table. Here is an example of using it:

=Count([InvoiceID])

In this case, this would return the number of records of the table.

The Avg() function is used to calculate the average value of the values of a column. It uses the same syntax as the Sum() function.

The Min() (or the Max()) function is used to calculate the minimum (or the maximum) of the values of a column. It uses the same syntax as the Sum() function.

The First() (or the Last()) function is used to find the first (or the last) value of a column among the records of a table.

Standard Number Formatting

 

Introduction

To display a value in a field, Microsoft Access primarily relies on the computer's Regional Options or Regional Settings that indicate how numbers should appear in an application. Numbers can be considered in various formats including accounting, scientific, fractions, and currency. Microsoft Access is configured to recognize and display numbers in any format of your choice. To do that, it uses some default settings. Still, if you want, you can control how a field should display its value and when.

Introduction to the Format() Number

To further assist with number formatting, the Visual Basic language provides a function named Format. This function can be used for different types of values The most basic technique consists of passing it an expression that holds the value to display. The syntax of this function is:

Function Format(ByVal Expression As Variant, _
   Optional ByVal Style As String = "" _
) As String

The first argument is the value that must be formatted. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number)
End Sub

This would produce:

Format

The second argument is optionally. It specifies the type of format you want to apply.

Visually Setting the Number Format

When creating a column of the table in Microsoft Access, you can specify how you want the field to display its numeric value. If you are creating the column in the Datasheet View of the table, you can first specify its Data Type as Number:

Number

After specifying the data type as Number, click the arrow of the Format combo box and select one of the options:

Number

As an option, after specifying the data type, if you want large numbers of the column to display a comma, on the Ribbon, click the Apply Comma Number Format Apply Comma Number Format. The thousand numbers would display with a comma sign which makes it easier to read.

To visually control the number of decimal values on the right side of the comma, still in the Data Type & Formatting section of the Ribbon:

  • You can click the Decrease Decimal button to remove one decimal value. You can continuously click the Decrease Decimal button to decrease the number of digits.
  • You can click the Increase Decimal button Increase Decimal  to increase the number of digits

If you want the field to display percentage values, click the Apply Percent Format button.

You can also work in the Design View. After specifying the field name, set its data type to Number. In the lower section of the window, click the arrow of the Format combo box and select one of the options.

Formatting a Number

To programmatically control how the number should display, you can pass the second argument to the Format() function. To produce the number in a general format, you can pass the second argument as "g", "G", "f", or "F" .

To display the number with a decimal separator, pass the second argument as "n", "N", or "Standard". Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "STANDARD")
End Sub

This would produce:

Format

An alternative to get this format is to call a function named FormatNumber. Its syntax is:

Function FormatNumber(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer,
   Optional ByVal UseParensForNegativeNumbers As Integer,
   Optional ByVal GroupDigits As Integer
) As String

Only the first argument is required and it represents the value to display. If you pass only this argument, you get the same format as the Format() function called with the Standard option. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox FormatNumber(Number)
End Sub

This would produce the same result as above.

If you call the Format() function with the Standard option, it would consider only the number of digits on the right side of the decimal separator. If you want to display more digits than the number actually has, call the FormatNumber() function and pass a second argument with the desired number. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox FormatNumber(Number, 4)
End Sub

This would produce:

Format

In the same way, if you want the number to display with less numbers on the right side of the decimal separator, specify that number.

We saw that you could click the Decrease Decimal button on the Ribbon to visually control the number of decimal values on the right side of the comma and you could continuously click that button to decrease the number of digits. Of course, you can also exercise this control programmatically.

You can call the Format() function to format the number with many more options. To represent the integral part of a number, you use the # sign. To specify the number of digits to display on the right side of the decimal separator, type a period on the right side of # followed by the number of 0s representing each decimal place. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "#.00000")
End Sub

This would produce:

Format

The five 0s on the right side of the period indicate that you want to display 5 digits on the right side of the period. You can enter as many # signs as you want; it would not change anything. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "##########.00000")
End Sub

This would produce the same result as above. To specify that you want to display the decimal separator, include its character between the # signs. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "###,#######.00000")
End Sub

This would produce:

Format

You can include any other character or symbol you want in the string to be part of the result, but you should include such a character only at the beginning or the end of the string, otherwise the interpreter might give you an unexpected result.

Percentage Number Formatting

 

Creating a Percentage Field

A percentage of a number represents its rate on a scale, usually of 100 (or more). The number is expressed using digits accompanied by the % sign.

To visually specify that a field should display its number a percentage value, if you are working in the Datasheet View of a table, first set its Data Type as Number:

Number

After specifying the data type as Number, click the arrow of the Format combo box and select the Percent option:

Number

Optionally, still in the Data Type & Formatting section of the Ribbon, click the Apply Percent Format button Apply Percent Format.

Formatting a Number as a Percentage

To programmatically use a percentage number in a cell or the control of a form, you can use the Format() function. Besides the Format() function, to support percent values, the Visual Basic language provides a function named FormatPercent. Its syntax is:

Function FormatPercent(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer = -2,
   Optional ByVal UseParensForNegativeNumbers As Integer = -2,
   Optional ByVal GroupDigits As Integer = -2
) As String

Only the first argument is required and it is the number that needs to be formatted. When calling this function, pay attention to the number you provide as argument. If the number represents a percentage value as a fraction of 0 to 1, make sure you provide it as such. An example would be 0.25. In this case, the Visual Basic interpreter would multiply the value by 100 to give the result. Here is an example:

Private Sub cmdFunction_Click()
    Dim DiscountRate As Double

    DiscountRate = 0.25
    MsgBox FormatPercent(DiscountRate)
End Sub

This would produce:

Percentage

If you pass the value in the hundreds, the interpreter would still multiply it by 100. Although it is not impossible to get a percentage value in the hundreds or thousands, you should make sure that's the type of value you mean to get.

Besides the FormatPercent() function, to format a number to its percentage equivalent, you can call the Format() function and pass the second argument as "Percent", "p", or "P". Here is an example:

Private Sub cmdFunction_Click()
    Dim DiscountRate As Double

    DiscountRate = 0.25
    MsgBox Format(DiscountRate, "Percent")
End Sub

Currency Formatting

 

Introduction

Another regular type of number used in applications and finances is the currency. A currency value uses a special character specified in the Control Panel. In US English, this character would be the $ sign Currency.

We have already seen how to declare a currency variable and how to configure a field for currency.

Formatting a Currency Value

To programmatically display the currency symbol in the result of a field or a text box on a form, you can simply add it as part of the second argument to the Format() function. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 205.5

    MsgBox Format(Number, "$###,#######.00")
End Sub

This would produce:

Format

Fortunately, there are more professional options. Besides the Format() function, to support currency formatting of a number, the Visual Basic language provides the FormatCurrency() function. Its syntax is:

Function FormatCurrency(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer = -2,
   Optional ByVal UseParensForNegativeNumbers As Integer = -2,
   Optional ByVal GroupDigits As Integer = -2
) As String

Only the first argument is required. It is the value that needs to be formatted. Here is an example:

Private Sub cmdFunction_Click()
    Dim UnitPrice As Double

    UnitPrice = 1450.5

    MsgBox FormatCurrency(UnitPrice)
End Sub

This would produce:

Format

Notice that, by default, the FormatCurrency() function is equipped to display the currency symbol (which, in US English is, the $ sign), the decimal separator (which in US English is the comma), and two decimal digits. If you want to control how many decimal digits are given to the result, pass a second argument as an integer. Here is an example:

Private Sub cmdFunction_Click()
    Dim UnitPrice As Double

    UnitPrice = 1450.5

    MsgBox FormatCurrency(UnitPrice, 4)
End Sub

This would produce:

Format

Instead of calling the FormatCurrency() function to format a number to currency, you can use the Format() function. If you do, pass it a second argument as "Currency", "c", or "C". Here is an example:

Private Sub cmdFunction_Click()
    Dim CarPrice As Double

    CarPrice = 42790

    MsgBox Format(CarPrice, "Currency")
End Sub

This would produce:

Format

 

 

 

 
   
 

Previous Copyright © 2005-2011 FunctionX, Inc. Next