Home

Domain Aggregate Functions

Introduction to Domain Aggregate Functions

Overview

A domain aggregate function is a function that conditionally acts on the records of a field to produce a statistical value. As opposed to regular SQL aggregate functions that are part of the structured query language (SQL), domain aggregate functions belong to Microsoft Access and are used to help you find a value based on a condition you set.

A domain aggregate function has the following syntax:

Public Function function-name(ByVal expression As Variant, ByVal domain As Variant, ByVal Optional condition As Boolean) As Variant

Every domain aggregate function has an appropriate name, the function-name. The expression argument can be the name of a column that holds the value on which to apply the function. It can also be a calculation-based expression. The domain argument can be the name of a table or of a query that doesn't require an external value.

The optional condition argument can specify the condition used to select (a) specific record(s). It acts as a WHERE condition. Therefore, a domain aggregate function resembles the function formula of a SQL statement as follows:

SELECT expression FROM domain WHERE condition

As mentioned already, Expression is usually the name if a column whose values would be considered. Domain can be the name of a table or query. The Criteria should be formulated as if it was following a WHERE operator.

Practical Learning: Introducing Domain Aggregate Functions

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. Set the Name to Quatro Gas Company
  4. Click Create
  5. On the default table, double-click ID to put it into edit mode
  6. Press F2 and press Home
  7. Type MeterReading to get MeterReadingID and press Enter
  8. Right-click the tab of the table and click Design View
  9. Set the name of the table to MetersReadings and click OK
  10. Cumplete the design of the table as follows:
    Field Name Data Type Field Size Format Caption
    MeterReadingID       Meter Reading ID
    MeterReadingDate Date/Time     Meter Reading Date
    AccountNumber   20   Account #
    MeterReadingValue Number Double Fixed Meter Reading Value
    ConsumptionValue Number Double Fixed Consumption Value
  11. On the Ribbon, click File and click Options
  12. In the left list of the Access Options dialog box, click Current Database
  13. In the main list, click Overlapping Windows
  14. Click OK on the dialog box
  15. Click OK on the message box
  16. On the Ribbon, click File and click Close
  17. In the list of files, click Quatro Gas
  18. On the Ribbon, click Create and, in the Tables section, click Table Design
  19. For the first field name, type MeterNumber and press Tab
  20. Right-click MeterNumber and click Primary Key
  21. Create the fields as follows:
    Field Name Data Type Field Size Format Caption
    MeterNumber   10   Meter #
    Make   25    
    Model   25    
    CounterValue Number Double Fixed  
  22. Save the table as GasMeters and close it
  23. On the Ribbon, click Create and, in the Tables section, click Table Design
  24. For the first field name, type AccountNumber and press Tab
  25. Right-click MeterNumber and click Primary Key
  26. Create the fields as follows:
    Field Name Field Size Caption
    AccountNumber 20 Account #
    MeterNumber 10 Meter #
    FirstName 20 First Name
    LastName 20 Last Name
    Address 100  
    City 30  
    County 32  
    State 2  
    ZIPCode 20 ZIP Code
  27. Save the table as Customers and close it
  28. On the Ribbon, click Create and click Query Design
  29. In the Show Table dialog box, click Close
  30. Right-click the body of the Query window and click SQL View
  31. Type: 
    CREATE TABLE GasBills
(
    InvoiceNumber COUNTER(100001),
    CONSTRAINT PK_GasBills PRIMARY KEY(InvoiceNumber)
);
  32. To create the table, on the Ribbon, click the Run button Run
  33. Close the Query window
  34. When asked whether you want to save, click No
  35. In the Navigation Pane, right-click GasBills and click Design View
  36. Create the other fields as follows:
    Field Name Data Type Field Size Format Caption
    InvoiceNumber       Invoice #
    AccountNumber   20   Account #
    ReadingStartDate Date/Time     Reading Start Date
    ReadingEndDate Date/Time     Reading End Date
    BillingDays Number     Billing Days
    MeterReadingStart Number Double Fixed Meter Reading Start
    MeterReadingEnd Number Double Fixed Meter Reading End
    ReadingDifference Number Double Fixed Reading Difference
    TotalTherms Number Double Fixed Total Therms
    TransportationCharge Number Double Fixed Transportation Charge
    DistributionAdjustment Number Double Fixed Distribution Adjustment
    DeliveryTotal Number Double Fixed Delivery Total
    EnvironmentalCharges Number Double Fixed Environmental Charges
    LocalTaxes Number Double Fixed Local Taxes
    StateTaxes Number Double Fixed State Taxes
    AmountDue Number Double Fixed Amount Due
  37. Save the GasBills table
  38. On the Ribbon, click Create and click Form Design
  39. In the Property Sheet, change the following characteristics:
    Caption: Quatro Gas Company - Meter Reading
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  40. Right-click the body of the form and click Form Header/Footer
  41. Save the form as Meter Reading
  42. Design the form as follows:

    Quatro Gas Company - Meter Counter Reading

    Control Caption Name Other Properties
    Label Label Quatro Gas Company - Meter Reading    
    Text Box Text Box Account #: txtAccountNumber  
    Text Box Text Box Customer Name: txtFirstName  
    Text Box Text Box   txtLastName  
    Text Box Text Box Address: txtAddress  
    Text Box Text Box   txtCity  
    Text Box Text Box   txtCounty  
    Text Box Text Box   txtState  
    Text Box Text Box   txtZIPCode  
    Line Line      
    Text Box Text Box Meter Reading Date: txtMeterReadingDate Format: Long Date
    Text Box Text Box Previous Meter Reading: txtPreviousMeterReading Format: Fixed
    Text Box Text Box Current Meter Reading: txtCurrentMeterReading Format: Fixed
    Text Box Text Box Consumption Value: txtConsumptionValue Format: Fixed
    Text Box Text Box Amount Due: txtAmountDue Format: Fixed
    Text Box Text Box Late Payment Amt: txtLatePaymentAmount Format: Fixed
    Button Button Submit cmdSubmit  
    Button Button Close cmdClose  
  43. On the form, click the txtAccountNumber text box
  44. In the Property Sheet, double-click On Lost Focus and click irts ellipsis button Browse
  45. Create a procedure as follows: 
    Private Sub ResetForm()
    txtFirstName = ""
    txtLastName = ""
    txtAddress = ""
    txtCity = ""
    txtCounty = ""
    txtState = ""
    txtZIPCode = ""
    txtAccountNumber = ""
    txtMeterReadingDate = Date
    txtPreviousMeterReading = "0.00"
    txtCurrentMeterReading = "0.00"
    txtConsumptionValue = "0.00"
End Sub

Private Sub txtAccountNumber_LostFocus()

End Sub
  46. In the Object combo box, select txtCurrentMeterReading
  47. In the Procedure combo box, select LostFocus
  48. Implement the event as follows: 
    Private Sub txtCurrentMeterReading_LostFocus()
    txtConsumptionValue = CDbl(Nz(txtCurrentMeterReading)) - CDbl(Nz(txtPreviousMeterReading))
End Sub
  49. Close Microsoft Visual Basic and return to Microsoft Access
  50. Save and close the Meter Reading form
  51. On the Ribbon, click Create and click Form Design
  52. In the Property Sheet, change the following characteristics:
    Caption: Quatro Gas Company - Invoice Preparation
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  53. Right-click the body of the form and click Form Header/Footer
  54. Save the form as Invoice Preparation
  55. Design the form as follows:

    Quatro Gas Company - Meter Counter Reading

    Control Caption Name Other Properties
    Label Label Quatro Gas Company - Customer Invoice    
    Text Box Text Box Account #: txtAccountNumber  
    Text Box Text Box Customer Name: txtFirstName  
    Text Box Text Box   txtLastName  
    Text Box Text Box Address: txtAddress  
    Text Box Text Box   txtCity  
    Text Box Text Box   txtCounty  
    Text Box Text Box   txtState  
    Text Box Text Box   txtZIPCode  
    Line Line      
    Label Label Meter Information   Back Color: Light Gray, Background 2, Darker 10%
    Text Box Text Box Meter Details: txtMeterDetails  
    Label Label Meter Reading    
    Line Line      
    Text Box Text Box Reading Start Date: txtReadingStartDate Format: Medium Date
    Text Box Text Box Reading End Date: txtReadingEndDate Format: Medium Date
    Button Button Submit cmdSubmit  
    Button Button Close cmdClose  
  56. On the form, click the txtAccountNumber text box
  57. In the Property Sheet, double-click On Lost Focus and click irts ellipsis button Browse
  58. Create a procedure as follows: 
    Private Sub ResetForm()
    txtFirstName = ""
    txtLastName = ""
    txtAddress = ""
    txtCity = ""
    txtCounty = ""
    txtState = ""
    txtZIPCode = ""
    txtMeterDetails = ""
End Sub

Private Sub txtAccountNumber_LostFocus()

End Sub

Looking Up a Value

Looking up a value in a table or query consists of visiting each one of the values of a certain field and finding out which one of those values matches the target. The aggregate function used to perform this operation is named DLookup. The DLookup() function can be used to find one or more records that respond to a criterion from another table or query.

As mentioned already, you can call this function without passing the third argument which sets the condition. If you omit that argument, all records from the omain would come in the result. This would be equivalent to a simple SELECT statement that doesn't have a condition.

As is the case for most functions, you can call DLookup() in the Control Source of a control in a form or report, or you can call it in your VBA code.

Practical Learning: Looking for a Record in a Domain

  1. In the Project window, double-click Form_Meter Reading
  2. Change the LostFocus event of the Account # text box as follows: 
    Private Sub txtAccountNumber_LostFocus()
    Dim strMeterNumber As String
    
    ' Access the Customers table
    ' Locate the customer whose account number was entered
    If Not IsNull(DLookup("AccountNumber", "Customers", _
                          "AccountNumber = '" & txtAccountNumber & "'")) Then
        strMeterNumber = DLookup("MeterNumber", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        ' If you find it, retrieve the information (first name, last name, etc)
        ' and display them
        txtFirstName = DLookup("FirstName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtLastName = DLookup("LastName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtAddress = DLookup("Address", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCity = DLookup("City", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCounty = DLookup("County", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtState = DLookup("State", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtZIPCode = DLookup("ZIPCode", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
    Else
        ' If you didn't find a customer with that account number, reset the form
        ResetForm
    End If
End Sub
  3. In the Project window, double-click Form_Invoice Preparation
  4. Change the LostFocus event of the Account # text box as follows:
    Private Sub txtAccountNumber_LostFocus()
    Dim strMeterNumber As String
    
    ' Access the Customers table
    ' Locate the customer whose account number was entered
    If Not IsNull(DLookup("AccountNumber", "Customers", _
                 "AccountNumber = '" & txtAccountNumber & "'")) Then
        ' If you find it, retrieve the information (first name, last name, etc)
        ' and display them
        strMeterNumber = DLookup("MeterNumber", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtFirstName = DLookup("FirstName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtLastName = DLookup("LastName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtAddress = DLookup("Address", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCity = DLookup("City", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCounty = DLookup("County", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtState = DLookup("State", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtZIPCode = DLookup("ZIPCode", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        
        txtMeterDetails = "Meter #: " & strMeterNumber & ", " & _
                          DLookup("Make", "GasMeters", "MeterNumber = '" & strMeterNumber & "'") & " " & _
                          DLookup("Model", "GasMeters", "MeterNumber = '" & strMeterNumber & "'")
    Else
        ResetForm
    End If
End Sub
  5. Close Microsoft Visual Basic and return to Microsoft Access
  6. Save and close the forms

A Review of Domain Aggregate Functions

The First of a Series

If you have a series of values that respond to a common criterion, to get the first value of that series, you can call the DFirst domain aggregate function. Remember that the optional third argument of the domain aggregate functions is a condition, similar to a WHERE clause. The condition can be as simple as something = value. It can also include cunjunctions, disjunctions, patterns, etc.

Practical Learning: Getting the First Record in a Series

  1. In the Navigation Pane, right-click the Invoice Preparation form and click Design View
  2. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  3. Change the caption of the accompanying text box to Counter Reading Start:
  4. Position the text box (and its label) below the other controls:

    Quatro Gas Company - The First Record in a Series

  5. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtMeterReadingStart
    Format: Fixed
  6. On the form, click the txtReadingEndDate text box
  7. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
  8. Implement the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
End Sub

The Last Value of a Domain

The DLast() function does the opposite of the DFirst() function: It retrieves the last value entered in a column of a form or report.

Practical LearningPractical Learning: Finding the Last Value of a Series

  1. In the Project window, double-click Form_Meter Reading
  2. Change the LostFocus event of the Account # text box as follows: 
    Private Sub txtAccountNumber_LostFocus()
    Dim strMeterNumber As String
    Dim PreviousMeterReading As Double
    
    ' Access the Customers table
    ' Locate the customer whose account number was entered
    If Not IsNull(DLookup("AccountNumber", "Customers", _
                          "AccountNumber = '" & txtAccountNumber & "'")) Then
        strMeterNumber = DLookup("MeterNumber", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        ' If you find it, retrieve the information (first name, last name, etc)
        ' and display them
        txtFirstName = DLookup("FirstName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtLastName = DLookup("LastName", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtAddress = DLookup("Address", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCity = DLookup("City", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtCounty = DLookup("County", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtState = DLookup("State", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        txtZIPCode = DLookup("ZIPCode", "Customers", "AccountNumber = '" & txtAccountNumber & "'")
        
        ' After the user has entered the customer account number, we need to know the previous reading of his gas counter.
        ' If/Since this is an existing customer, get the last counter value of his gas meter
        PreviousMeterReading = Nz(DLast("MeterReadingValue", "MetersReadings", "AccountNumber = '" & txtAccountNumber & "'"))
        
        ' If no value was read from the gas meter, this is probably the first time the counter is read.
        If PreviousMeterReading = 0# Then
            ' In this case, simply get the initial value of the gas meter that
            ' was entered when the record of the gas meter was created.
            ' Put that value in the Previous Meter Reading text box
            txtPreviousMeterReading = DLookup("CounterValue", "GasMeters", "MeterNumber = '" & strMeterNumber & "'")
        Else
            ' If a value was read from the gas meter, display that value in the Previous Meter Reading text box
            txtPreviousMeterReading = PreviousMeterReading
        End If
        
        ' Before the user puts the new value of the gas meter counter, put the value of the previous reading
        txtCurrentMeterReading = PreviousMeterReading
        ' Also, don't leave the Consumption Value text box empty
        txtConsumptionValue = "0.00"
    Else
        ' If you didn't find a customer with that account number, reset the form
        ResetForm
    End If
End Sub
  3. Close Microsoft Visual Basic and return to Microsoft Access
  4. Save and close the Meter Reading form
  5. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  6. Change the caption of the accompanying text box to Counter Reading End:
  7. Position the text box (and its label) on the right side of the previously added text box:

    Quatro Gas Company - The First Record in a Series

  8. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtMeterReadingEnd
    Format: Fixed
  9. On the form, click the txtReadingEndDate text box
  10. In the Property Sheet, click On Lost Focus and click its ellipsis button
  11. Change the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
End Sub
  12. In the Project window, double-click Form_Meter Reading
  13. In the Object combo box, select cmdSubmit
  14. Implement the event as follows: 
    Private Sub cmdSubmit_Click()
    Dim dbGasCompany As Database
    Dim rsConsumption As Recordset
        
    If IsNull(txtAccountNumber) Then
        MsgBox "You must specify the account number of the customer/consumer.", _
               VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
               "Gas Utility Company"
        Exit Sub
    End If
    
    If IsNull(txtMeterReadingDate) Or Not IsDate(txtMeterReadingDate) Then
        MsgBox "You must specify the date the gas was used.", _
               VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
               "Gas Utility Company"
        Exit Sub
    End If
    
    If IsNull(txtCurrentMeterReading) Then
        MsgBox "You must enter 0 or the current reading value of the gas meter.", _
               VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
               "Gas Utility Company"
        txtCurrentMeterReading = "0.00"
    End If
    
    Set dbGasCompany = CurrentDb
    Set rsConsumption = dbGasCompany.OpenRecordset("MetersReadings", _
                                                    RecordsetTypeEnum.dbOpenTable, _
                                                    RecordsetOptionEnum.dbDenyRead, _
                                                    LockTypeEnum.dbPessimistic)
    rsConsumption.AddNew
    rsConsumption!MeterReadingDate = CDate(txtMeterReadingDate)
    rsConsumption!AccountNumber = txtAccountNumber
    rsConsumption!MeterReadingValue = IIf(IsNull(txtCurrentMeterReading), 0, CDbl(Nz(txtCurrentMeterReading)))
    rsConsumption!ConsumptionValue = CDbl(Nz(txtConsumptionValue))
    rsConsumption.Update
    
    MsgBox "The consumption record has been saved.", _
           VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
           "Gas Utility Company"
        
    Set rsConsumption = Nothing
    Set dbGasCompany = Nothing
    
    ' After saving the consumption, reset the form
    ResetForm
End Sub
  15. In the Object combo box, select cmdClose
  16. Implement the event as follows: 
    Private Sub cmdClose_Click()
    DoCmd.Close
End Sub
  17. Close Microsoft Visual Basic and return to Microsoft Access
  18. Save and close the forms
  19. In the Navigation Pane, double-click the GasMeters table
  20. Create the following records:
    Meter # Make Model Counter Value
    293847-27 Archimeda LP2066 2866
    928731-59 EnvioSmart 84-D9703 8016
    797047-27  Archimeda LP2066 725
    582741-38 Sinton International D-244 138
    425837-14  EnvioSmart 28-G4428 6114
  21. Close the GasMeters table
  22. In the Navigation Pane, double-click the Customers table
  23. Create the following records:
    Account # Meter # First Name Last Name Address City County State ZIP Code
    60-9720-824 293847-27 Jefferey Parriot 688 Gorman St Rockville Montgomery MD 20856
    82-1397-851 425837-14 Christopher McGee 3316 Sanderson Rd Alexandria   VA 22314
    82-9360-597 582741-38 Sandra Rhodes 11158 Grattos Ave Hyattsville Prince George MD 20783
    29-5384-805 928731-59 Marco Ramos 9012 Jefferson Crt Washington   DC 20012
    92-3848-393 797047-27 Gabrielle Bayley 799 Boomerang Str Columbia Howard MD 21045
  24. Close the Customers table
  25. In the Navigation Pane, double-click the Meter Reading form
  26. Enter the Account # as 60-9720-824 and press Tab

    Quatro Gas Company - The First Record in a Series

  27. Enter the Reading Start Date for 18 January 2017 and press Tab
  28. Enter the Reading End Date as 2869.38 and press Tab.
    The Consumption Value should have been calculated as 3.38

    Quatro Gas Company - The First Record in a Series

  29. Click the Submit button and click OK on the message box
  30. Enter the Account # as 60-9720-824 and press Tab
  31. Enter the Meter Reading Date for January 19, 2017 and press Tab
  32. Enter the Current Meter Reading as 2871.37 and press Tab.
    The Consumption Value should have been calculated as 1.99
  33. Click the Submit button and click OK on the message box.
    The resources that accompany these lessons include a spreadsheet named Quatro Gas Company. That spreadsheet includes a section titled Meter Reading. It contains 359 records. Create those records using the Meter Reading form. You can also import them. One option is to select and copy all those records in a text editor such as Notepad, save the file with a txt extension, then import that file. Another option is to create a name for the section in the spreadsheet, then import the spreadsheet in Microsoft Access but import only the named area
  34. Close the Meter Reading form
  35. In the Navigation Pane, double-click the Invoice Preparation form
  36. Enter the Account # as 60-9720-824 and press Tab

    Quatro Gas Company - The First Record in a Series

  37. Enter the Reading Start Date for 18 January 2017 and press Tab
  38. Enter the Reading End Date as 20 February 2017 and press Tab

    Quatro Gas Company - The First Record in a Series

  39. Enter the Account # as 82-9360-597 and press Tab
  40. Enter the Meter Reading Start Date January 20, 2017 and press Tab
  41. Enter the Current Reading End Date for 22 February 2017 and press Tab

    Quatro Gas Company - The First Record in a Series

  42. Close the Invoice Preparation form

The Minimum Value of a Series

The DFirst (DLast) function is used to get the first (last) of a series values of any types, including strings. If the values are number-based or date/time type, to find the lowest value in the series, which may not be the first, you can call the DMin() function.

Practical LearningPractical Learning: Finding the Lowest Value of a Series

  1. In Navigation Pane, right-click the Invoice Preparation form and click Design View
  2. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  3. Change the caption of the accompanying text box to Lowest Consumption:
  4. Position the new text box (and its label) below the previously added text boxes:

    Quatro Gas Company - The Lowest Value of a Series

  5. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtLowestConsumption
    Format: Fixed
  6. On the form, click the txtReadingEndDate text box
  7. In the Property Sheet, click On Lost Focus and click its ellipsis button
  8. Change the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
    txtLowestConsumption = DMin("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
End Sub
  9. Close Microsoft Visual Basic and return to Microsoft Access

The Highest Value of a Domain

As opposed to the DMin() function, the DMax() function gets the highest value of a series.

Practical LearningPractical Learning: Finding the Highest Value of a Series

  1. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  2. Change the caption of the accompanying text box to Highest Consumption:
  3. Position the text box (and its label) on the right side of the previously added text box:

    Quatro Gas Company - The Highest Value of a Series

  4. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtHighestConsumption
    Format: Fixed
  5. On the form, click the txtReadingEndDate text box
  6. In the Property Sheet, click On Lost Focus and click its ellipsis button
  7. Change the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
    txtLowestConsumption = DMin("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtHighestConsumption = DMax("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access
  9. Switch the form to Form View
  10. Enter the Account # as 60-9720-824 and press Tab
  11. Enter the Reading Start Date for 18 January 2017 and press Tab
  12. Enter the Reading End Date as 20 February 2017 and press Tab

    Quatro Gas Company - The Highest Value of a Series

  13. Enter the Account # as 92-3848-393 and press Tab
  14. Enter the Meter Reading Start Date January 24, 2017 and press Tab
  15. Enter the Current Reading End Date for 21 February 2017 and press Tab

    Quatro Gas Company - The Highest Value of a Series

  16. Switch the form to Design View

The Number of Records

Probably the most fundamental numeric information you may want to know about a series is the number of records it contains. This information can be provided by an aggregate function named DCount. When calling this function, the first argument should always be the name of a column. Here is an example:

=DCount("Title", "Videos")

This code returns the total number of videos that have a value in the Title column. The DCount() function doesn't count the records where the Expression value is NULL. As mentioned in the syntax of the domain aggregate functions, you can use a criterion to select specific records. Here is an example:

=DCount("Title", "Videos", "Rating = 'R'")

In this case, the function will return the number of videos that are rated R from our table.

Practical Learning: Getting the Number of Records

  1. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  2. Change the caption of the accompanying text box to Number of Days:
  3. Position the new text box (and its label) below the previously added text boxes:

    Quatro Gas Company - The Highest Value of a Series

  4. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtNumberOfDays
    Format: Standard
    Decimal Places: 0
  5. On the form, click the txtReadingEndDate text box
  6. In the Property Sheet, click On Lost Focus and click its ellipsis button
  7. Change the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
    txtNumberOfDays = DCount("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtLowestConsumption = DMin("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtHighestConsumption = DMax("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access

The Sum of Values

The DSum() function can calculate the total of (numeric) values of a column in a table (or query). Here is an example that will return the amount we spent buying the videos stored in a table:

=DSum("PriceBought","Videos")

Practical Learning: Getting the Sum of the Values of a Field

  1. In the Controls section of the Ribbon, click the Text Box Text Box and click the form
  2. Change the caption of the accompanying text box to CCF Total:
  3. Position the new text box (and its label) below the previously added text boxes:

    Quatro Gas Company - The Sum of the Values of a Field

  4. In the Property Sheet, change the characteristics of the text box as follows:
    Name: txtCCFTotal
    Format: Fixed
  5. On the form, click the txtReadingEndDate text box
  6. In the Property Sheet, click On Lost Focus and click its ellipsis button
  7. Change the event as follows:
    Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
    txtNumberOfDays = DCount("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtLowestConsumption = DMin("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtHighestConsumption = DMax("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtCCFTotal = DSum("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access

The Average of Values

The DAvg() function is used to calculate the average of (numeric) values of a column of a table. Here is an example that produces the average price of the videos of the above table:

=DAvg("PriceBought","Videos")

Practical LearningPractical Learning: Finding the Average Value of a Series

  1. Complete the design of the Invoice Preparation form as follows:

    Quatro Gas Company - Meter Counter Reading

    Control Caption Name Other Properties
    Text Box Text Box Mean Consumption: txtMeanConsumption Format: Fixed
    Text Box Text Box Total Therms (CCF * 1.0367): txtTotalTherms Format: Fixed
    Line Line      
    Label Label Bill Values   Back Color: Light Gray, Background 2, Darker 10%
    Text Box Text Box Customer Charges: txtTransportationCharge Format: Fixed
    Text Box Text Box Delivery Total: txtDeliveryTotal Format: Fixed
    Text Box Text Box Distrib Adjust (* 0.13086): txtDistributionAdjustment Format: Fixed
    Text Box Text Box Environmental Charges: txtEnvironmentalCharges Format: Fixed
    Text Box Text Box First 50 Therms (* 0.5269): txtFirst50Therms Format: Fixed
    Text Box Text Box Local/County Taxes: txtLocalTaxes Format: Fixed
    Text Box Text Box Over 50 Therms (* 0.4995): txtOver50Therms Format: Fixed
    Text Box Text Box State Taxes: txtStateTaxes Format: Fixed
    Line Line      
    Text Box Text Box Amount Due: txtAmountDue Format: Fixed
  2. On the form, click the txtReadingEndDate text box
  3. In the Property Sheet, click On Lost Focus and click its ellipsis button
  4. Change the ResetForm procedure as follows: 
    Private Sub ResetForm()
    txtFirstName = ""
    txtLastName = ""
    txtAddress = ""
    txtCity = ""
    txtCounty = ""
    txtState = ""
    txtZIPCode = ""
    txtAccountNumber = ""
    txtMeterDetails = ""
    
    txtMeterReadingStart = "0.00"
    txtMeterReadingEnd = "0.00"
    txtNumberOfDays = "0.00"
    txtLowestConsumption = "0.00"
    txtHighestConsumption = "0.00"
    txtCCFTotal = "0.00"
    txtMeanConsumption = "0.00"
    
    txtCCFTotal = "0.00"
    txtTotalTherms = "0.00"
    txtDistributionAdjustment = "0.00"
    txtFirst50Therms = "0.00"
    txtOver50Therms = "0.00"
    txtDeliveryTotal = "0.00"
    txtEnvironmentalCharges = "0.00"
    txtLocalTaxes = "0.00"
    txtStateTaxes = "0.00"
    txtAmountDue = "0.00"
End Sub
  5. Create a procedure above the LostFocus event of the txtReadingEndDate text box and change the event as follows:
    Private Sub ProcessInvoice()
    Dim AmountDue As Double
    Dim TotalTherms  As Double
    Dim DeliveryTotal As Double
    Dim CCFTotal As Long
    Dim EnvironmentalCharges As Double
    Dim DistributionAdjustment As Double
    Dim LocalTaxes As Double, StateTaxes As Double
    Dim First50Therms As Double, Over50Therms As Double
    
    If IsNull(txtMeterReadingStart) Then
        Exit Sub
    End If
    
    If IsNull(txtMeterReadingEnd) Then
        Exit Sub
    End If
    
    txtTransportationCharge = "10.55"
    
    CCFTotal = CLng(txtMeterReadingEnd) - CLng(txtMeterReadingStart)
    TotalTherms = CCFTotal * 1.0367
    DistributionAdjustment = TotalTherms * 0.13086
    
    If TotalTherms < 50 Then
        First50Therms = TotalTherms * 0.5269
        Over50Therms = 0#
    Else
        First50Therms = 50 * 0.5269
        Over50Therms = (TotalTherms - 50) * 0.4995
    End If
    
    DeliveryTotal = CDbl(Nz(txtTransportationCharge)) + DistributionAdjustment + First50Therms + Over50Therms
    EnvironmentalCharges = DeliveryTotal * 0.0045
    LocalTaxes = DeliveryTotal * 0.05
    StateTaxes = DeliveryTotal * 0.1
    AmountDue = DeliveryTotal + EnvironmentalCharges + LocalTaxes + StateTaxes
    
    txtCCFTotal = Format(CCFTotal, "STANDARD")
    txtTotalTherms = FormatNumber(TotalTherms)
    txtDistributionAdjustment = FormatNumber(DistributionAdjustment)
    txtFirst50Therms = FormatNumber(First50Therms)
    txtOver50Therms = FormatNumber(Over50Therms)
    txtDeliveryTotal = FormatNumber(DeliveryTotal)
    txtEnvironmentalCharges = FormatNumber(EnvironmentalCharges)
    txtLocalTaxes = FormatNumber(LocalTaxes)
    txtStateTaxes = FormatNumber(StateTaxes)
    txtAmountDue = FormatNumber(AmountDue)
End Sub

Private Sub txtReadingEndDate_LostFocus()
    If IsNull(txtReadingStartDate) Then
        Exit Sub
    End If
    
    If IsNull(txtReadingEndDate) Then
        Exit Sub
    End If
    
    txtMeterReadingStart = DFirst("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingStartDate) & "#)")
    txtMeterReadingEnd = DLast("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate = #" & CDate(txtReadingEndDate) & "#)")
    txtNumberOfDays = DCount("MeterReadingValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtLowestConsumption = DMin("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtHighestConsumption = DMax("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtCCFTotal = DSum("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    txtMeanConsumption = DAvg("ConsumptionValue", "MetersReadings", "(AccountNumber = '" & txtAccountNumber & "') AND (MeterReadingDate BETWEEN #" & CDate(txtReadingStartDate) & "# AND #" & CDate(txtReadingEndDate) & "#)")
    
    ProcessInvoice
End Sub
  6. In the Object combo box, select cmdSubmit
  7. Implment the event as follows: 
    Private Sub cmdSubmit_Click()
    Dim dbWattsALoan As Database
    Dim rsGasBills As Recordset
    
    Set dbWattsALoan = CurrentDb
    Set rsGasBills = dbWattsALoan.OpenRecordset("GasBills", _
                                                RecordsetTypeEnum.dbOpenTable, _
                                                RecordsetOptionEnum.dbDenyRead, _
                                                LockTypeEnum.dbPessimistic)
    
    If IsNull(txtAccountNumber) Or _
       IsNull(txtReadingStartDate) Or _
       IsNull(txtReadingEndDate) Or _
       IsNull(txtMeterReadingStart) Or _
       IsNull(txtMeterReadingEnd) Then
        Exit Sub
    End If
    
    rsGasBills.AddNew
    rsGasBills!AccountNumber = txtAccountNumber
    rsGasBills!ReadingStartDate.Value = txtReadingStartDate
    rsGasBills!ReadingEndDate.Value = txtReadingStartDate
    rsGasBills![BillingDays] = txtNumberOfDays
    rsGasBills![MeterReadingStart] = txtMeterReadingStart
    rsGasBills![MeterReadingEnd] = txtMeterReadingEnd
    rsGasBills![readingDifference] = txtCCFTotal
    rsGasBills![TotalTherms] = txtTotalTherms
    rsGasBills![TransportationCharge] = txtTransportationCharge
    rsGasBills![DistributionAdjustment] = txtDistributionAdjustment
    rsGasBills!DeliveryTotal = txtDeliveryTotal
    rsGasBills!EnvironmentalCharges = txtEnvironmentalCharges
    rsGasBills!LocalTaxes = txtLocalTaxes
    rsGasBills!StateTaxes = txtStateTaxes
    rsGasBills!AmountDue = txtAmountDue
    rsGasBills.Update

    rsGasBills.Close
    dbWattsALoan.Close
    
    MsgBox "The customer's invoice has been preparedd, approved, and saved.", _
           VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
           "Gas Utility Company"
    
    DoCmd.Close
End Sub
  8. In the Object combo box, select cmdClose
  9. Implement the event as follows: 
    Private Sub cmdClose_Click()
    DoCmd.Close
End Sub
  10. Close Microsoft Visual Basic and return to Microsoft Access
  11. Save and close the Invoice Preparation form
  12. In the Navigation Pane, double-click the Invoice Preparation form
  13. Enter the Account # as 60-9720-824 and press Tab
  14. Enter the Reading Start Date for 18 January 2017 and press Tab
  15. Enter the Reading End Date as 20 February 2017 and press Tab

    Quatro Gas Company - The Average Value of a Series

  16. Click the Submit button
  17. Click OK on the message box
  18. In the Navigation Pane, double-click the Invoice Preparation form
  19. Enter the Account # as 92-3848-393 and press Tab
  20. Enter the Meter Reading Start Date January 24, 2017 and press Tab
  21. Enter the Current Reading End Date for 21 February 2017 and press Tab

    Quatro Gas Company - The Highest Value of a Series

  22. Click the Submit button
  23. Click OK on the message box
  24. In the Navigation Pane, double-click the Invoice Preparation form
  25. Enter the Account # as 82-9360-597 and press Tab
  26. Enter the Meter Reading Start Date 20 January 2017 and press Tab
  27. Enter the Current Reading End Date for 22 February 2017 and press Tab

    Quatro Gas Company - The Highest Value of a Series

  28. Click the Submit button
  29. Click OK on the message box

The Standard Deviation

The StDev() function is used to calculate the standard deviation of the numeric values of the records of a table or query. The DStDevP() function is used to calculate the standard deviation of a population sample of the numeric values of the records of a table or query. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group.

The Variance

The DVar() function calculates the statistical variance of the (numeric) values of a table or query. The DVarP() function calculates the variance of a population. If there is no value or the same value in the considered group, these functions return NULL.

Practical Learning: Ending the Lesson
Previous Copyright © 2000-2022, FunctionX, Inc. Next