UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Domain Functions    
Domain Functions

Contents

Domain Functions

Domain Functions have been used since the inception of Access and have proven very handy. A "Domain Function" is technically referred to as a 'Domain Aggregate Function' and are interchangable.

'Domain Aggregate' functions provide statistical information about sets of records (a domain - table or query). Typically, you can use an aggregate function to count the number of records, to sum the values of a specific field or to determine the average of values in a particular field.

There are two types of aggregate functions, 'Domain aggregate functions' and 'SQL aggregate functions'. They provide similar functionality but are used in different situations. Domain aggregate functions can be called directly from VBA and can be included in an SQL statement, but an 'SQL aggregate' function is generally more efficient. 'SQL aggregate' functions can be included in the syntax of an SQL statement but cannot be called directly from VBA.

If you are performing statistical calculations from within code, you must use the domain aggregate functions. You can also use the domain aggregate functions to specify criteria, update values, or create calculated fields in a query expression. You can use either the 'SQL aggregate' or 'domain aggregate' functions within calculated controls in forms or reports.

A typical Domain function that uses string criteria has a syntax similar to this:

DFunction("[Expression]","Domain","[FieldToMatchCriteria] = '" & ReferenceToSomeStringValue & "'")

A typical Domain function that uses numeric criteria has a syntax similar to this:

DFunction("[Expression]","Domain","[FieldToMatchCriteria] = " & ReferenceToSomeNumericValue)

The black lettering is the name of the specific domain function and always wraps all three sections in parenthsis. The two commas (in red) separate the three sections. All three sections are each wrapped in a set of double-quotes.

The first section (in purple) is where the developer states the name of the field whose value is being sought.

The second section (in green) is where the developer states the name of the table or query (the domain) from which the field value is being sought. This second section only works with a table or a query.

The third section (in blue) is where the developer establishes what criteria must be matched against what field or fields in the second section's declared table or query. The Criteria is in brown.

The ReferenceToSomeOtherValue can be another Domain Function, a reference to a control on a form or report, a User Created Function, a declared public variable or even a static number or text. This section can also be very tricky, depending on what data type the developer is trying to match in the criteria settings.


And before digging into the default Access Domain Functions, there are a couple of other modified "Domain Functions" written by Access MVP's here:

Allen Browne's Extended DLookUp funtion: http://allenbrowne.com/ser-42.html

and

theDBguy's DDiff() function - opposite of DSum(): http://accessmvp.com/thedbguy/codes.asp?title=ddiff


The default Access domain aggregate functions include:

DAvg - calculate the 'average' of a set of values from within a specified set of records
DCount - acquire the 'count' of records from within a specified set of records
DLookup - 'look up' the value of a particular field from within a specified set of records
DFirst & DLast - acquire the 'first' or 'last' value of a field from within a specified set of records
DMin & DMax - acquire the 'minimum' or 'maximum' value of a field from within a specified set of records
DStDev & DStDevP - estimate the 'standard deviation' across a set of values from within a specified set of records
DSum - calculate the 'sum' of a set of values from within a specified set of records
DVar & DVarP - estimate 'variance' across a set of values from within a specified set of records

DAvg

You can use the DAvg function to calculate the average of a set of values in a specified set of records (a domain). Use the DAvg function in Visual Basic code or in a macro, in a query expression, or in a calculated control.

For example, you could use the DAvg function in the criteria row of a select query on freight cost to restrict the results to those records where the freight cost exceeds the average. Or you could use an expression including the DAvg function in a calculated control and display the average value of previous orders next to the value of a new order.

DAvg(expr, domain, [criteria]) The DAvg function has the following arguments.

Argument Description

expr - An expression that identifies the field containing the numeric data you want to average. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DAvg function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DAvg function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DAvg function returns a Null.

Remarks

Records containing Null values aren't included in the calculation of the average.

Whether you use the DAvg function in a macro or module, in a query expression, or in a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DAvg function to specify criteria in the Criteria row of a query. For example, suppose you want to view a list of all products ordered in quantities above the average order quantity. You could create a query on the tblOrders, tblOrderDetails, and tblProducts tables, and include the ProductName field and the Quantity field, with the following expression in the Criteria row beneath the Quantity field:

>DAvg("[Quantity]", "tblOrders")

You can also use the DAvg function within a calculated field expression in a query, or in the Update To row of an update query.

Note You can use either the DAvg or Avg function in a calculated field expression in a totals query. If you use the DAvg function, values are averaged before the data is grouped. If you use the Avg function, the data is grouped before values in the field expression are averaged.

Use the DAvg function in a calculated control when you need to specify criteria to restrict the range of data on which the DAvg function is performed. For example, to display the average cost of freight for shipments sent to California, set the ControlSource property of a text box to the following expression:

=DAvg("[Shipping]", "tblOrders", "[ShipRegion] = 'CA'")

If you simply want to average all records in domain, use the Avg function.

You can use the DAvg function in a module or macro or in a calculated control on a form if a field that you need to display isn't in the record source on which your form is based. For example, suppose you have a form based on a tblOrders table, and you want to include the Quantity field from the Order Details table in order to display the average number of items ordered by a particular customer. You can use the DAvg function to perform this calculation and display the data on your form.

Tips:

If you use the DAvg function in a calculated control, you may want to place the control on the form header or footer so that the value for this control is not recalculated each time you move to a new record. If the data type of the field from which expr is derived is a number, the DAvg function returns a Double data type. If you use the DAvg function in a calculated control, include a data type conversion function in the expression to improve performance. Although you can use the DAvg function to determine the average of values in a field in a foreign table, it may be more efficient to create a query that contains all of the fields that you need and then base your form or report on that query. Note Unsaved changes to records in domain aren't included when you use this function. If you want the DAvg function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following function returns the average shipping cost for orders shipped on or after a given date. The domain is a tblOrders table. The criteria argument restricts the resulting set of records based on the given country and ship date. Note that the keyword AND is included in the string to separate the multiple fields in the criteria argument. All records included in the DAvg function calculation will have both of these criteria.

CODE
Public Function AvgShippingCost(ByVal strCountry As String, ByVal dteShipDate As Date) As Double

   AvgFreightCost = DAvg("[Shipping]", "tblOrders", _
                    "[ShipCountry] = '" & strCountry & _
                    "'AND [ShippedDate] >= #" & dteShipDate & "#")

End Function


To call the function, use the following line of code in the Immediate window:

AvgShippingCost "UK", #1/1/2006#

DCount

You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control.

For example, you could use the DCount function in a module to return the number of records in a tblOrders table that correspond to orders placed on a particular date.

DCount(expr, domain, [criteria]) The DCount function has the following arguments.

Argument Description

expr - An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.

Remarks

Use the DCount function to count the number of records in a domain when you don't need to know their particular values. Although the expr argument can perform a calculation on a field, the DCount function simply tallies the number of records. The value of any calculation performed by expr is unavailable.

Use the DCount function in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display the number of orders to be shipped to California, set the ControlSource property of a text box to the following expression:

=DCount("[OrderID]", "tblOrders", "[ShipRegion] = 'CA'")

If you simply want to count all records in domain without specifying any restrictions, use the Count function.


Tip

The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.

You can use the DCount function to count the number of records containing a particular field that isn't in the record source on which your form or report is based. For example, you could display the number of orders in the tblOrders table in a calculated control on a form based on the Products table.

The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields. The following example calculates the number of records in a tblOrders table.

intX = DCount("*", "tblOrders")

If domain is a table with a primary key, you can also count the total number of records by setting expr to the primary key field, since there will never be a Null in the primary key field.

If expr identifies multiple fields, separate the field names with a concatenation operator, either an ampersand (&) or the addition operator (+). If you use an ampersand to separate the fields, the DCount function returns the number of records containing data in any of the listed fields. If you use the addition operator, the DCount function returns only the number of records containing data in all of the listed fields. The following example demonstrates the effects of each operator when used with a field that contains data in all records (ShipName) and a field that contains no data (ShipRegion).

intW = DCount("[ShipName]", "tblOrders") intX = DCount("[ShipRegion]", "tblOrders") intY = DCount("[ShipName] + [ShipRegion]", "tblOrders") intZ = DCount("[ShipName] & [ShipRegion]", "tblOrders")

Note: The ampersand is the preferred operator for performing string concatenation. You should avoid using the addition operator for anything other than numeric addition, unless you specifically wish to propagate Nulls through an expression.

Unsaved changes to records in domain aren't included when you use this function. If you want the DCount function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following function returns the number of orders shipped to a specified country after a specified ship date. The domain is a tblOrders table.

CODE
Public Function OrdersCount(ByVal strCountry As String, ByVal dteShipDate As Date) As Integer

   OrdersCount = DCount("[ShippedDate]", "tblOrders", _
                 "[ShipCountry] = '" & strCountry & _
                 "' AND [ShippedDate] > #" & dteShipDate & "#")
End Function


To call the function, use the following line of code in the Immediate window:

OrdersCount "UK", #1/1/96#


DLookup

You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

DLookup(expr, domain, [criteria]) The DLookup function has the following arguments.

Argument Description

expr - An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.


Remarks

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:

CODE
Dim varX As Variant
varX = DLookup("[LastName]", "tblEmployees", "[EmployeeID] = 1")

Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query.

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "tblProducts", "[ProductID] =" & Forms![frmOrderDetails]!ProductID)

Tips:

Although you can use the DLookup function to display a value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables and then to base your form or report on that query. You can also use the Lookup Wizard to find values in a foreign table. Note Unsaved changes to records in domain aren't included when you use this function. If you want the DLookup function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.

CODE
Dim varX As Variant
varX = DLookup("[CompanyName]", "tblShippers", "[ShipperID] = 1")

The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function. Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control.

CODE
Dim varX As Variant
varX = DLookup("[CompanyName]", "tblShippers", "[ShipperID] = " & Forms!frmShippers!ShipperID)

The next example uses a variable, intSearch, to get the value.

CODE
Dim intSearch As Integer
Dim varX As Variant

intSearch = 1
varX = DLookup("[CompanyName]", "tblShippers", "[ShipperID] = " & intSearch)


DFirst & DLast

You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro, module, query expression, or calculated control on a form or report.

DFirst(expr, domain, [criteria]) DLast(expr, domain, [criteria]) The DFirst and DLast functions have the following arguments.

Argument Description

expr - An expression that identifies the field from which you want to find the first or last value. It can be either a string expression identifying a field in a table or query, or an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain.

criteria - An optional string expression used to restrict the range of data on which the DFirst or DLast function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DFirst and DLast functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DFirst and DLast functions return a Null.


Remarks

Note: If you want to return the first or last record in a set of records (a domain), you should create a query sorted as either ascending or descending and set the TopValues property to 1. For more information, see the TopValues property topic. From Visual Basic, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.


DMin & DMax

You can use the DMin and DMax functions to determine the minimum and maximum values in a specified set of records (a domain). Use the DMin and DMax functions in Visual Basic, a macro, a query expression, or a calculated control.

For example, you could use the DMin and DMax functions in calculated controls on a report to display the smallest and largest order amounts for a particular customer. Or you could use the DMin function in a query expression to display all orders with a discount greater than the minimum possible discount.

DMin(expr, domain, [criteria]) DMax(expr, domain, [criteria]) The DMin and DMax functions have the following arguments.

Argument Description

expr - An expression that identifies the field for which you want to find the minimum or maximum value. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DMin or DMax function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DMin and DMax functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain, otherwise the DMin and DMax functions returns a Null.


Remarks

The DMin and DMax functions return the minimum and maximum values that satisfy criteria. If expr identifies numeric data, the DMin and DMax functions return numeric values. If expr identifies string data, they return the string that is first or last alphabetically.

The DMin and DMax functions ignore Null values in the field referenced by expr. However, if no record satisfies criteria or if domain contains no records, the DMin and DMax functions return a Null.

Whether you use the DMin or DMax function in a macro, module, query expression, or calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DMin and DMax function to specify criteria in the Criteria row of a query, in a calculated field expression in a query, or in the Update To row of an update query.

Note: You can use the DMin and DMax functions or the Min and Max functions in a calculated field expression of a totals query. If you use the DMin or DMax function, values are evaluated before the data is grouped. If you use the Min or Max function, the data is grouped before values in the field expression are evaluated.

Use the DMin or DMax function in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display the maximum freight charged for an order shipped to California, set the ControlSource property of a text box to the following expression:

=DMax("[Shipping]", "tblOrders", "[ShipRegion] = 'CA'")

If you simply want to find the minimum or maximum value of all records in domain, use the Min or Max function.

You can use the DMin or DMax function in a module or macro or in a calculated control on a form if the field that you need to display is not in the record source on which your form is based.


Tip

Although you can use the DMin or DMax function to find the minimum or maximum value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables and base your form or report on that query.

Note: Unsaved changes to records in domain aren't included when you use these functions. If you want the DMax or DMin function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following example returns the lowest and highest values from the Shipping field for orders shipped to the United Kingdom. The domain is a tblOrders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.

Dim curX As Currency Dim curY As Currency

curX = DMin("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'") curY = DMax("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

In the next example, the criteria argument includes the current value of a text box called OrderDate. The text box is bound to an OrderDate field in a tblOrders table. Note that the reference to the control isn't included in the double quotation marks (") that denote the strings. This ensures that each time the DMax function is called, Microsoft Access obtains the current value from the control.

CODE
Dim curX As Currency
curX = DMax("[Shipping]", "tblOrders", "[OrderDate] = #" & Forms!frmOrders!OrderDate & "#")

In the next example, the criteria expression includes a variable, dteOrderDate. Note that number signs (#) are included in the string expression, so that when the strings are concatenated, they will enclose the date.

CODE
Dim dteOrderDate As Date
Dim curX As Currency

dteOrderDate = #03/30/2000#
curX = DMin("[Shipping]", "tblOrders", "[OrderDate] = #" & dteOrderDate & "#"


DStDev & DStDevP

You can use the DStDev and DStDevP functions to estimate the standard deviation across a set of values in a specified set of records (a domain). Use the DStDev and DStDevP functions in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

Use the DStDevP function to evaluate a population and the DStDev function to evaluate a population sample.

For example, you could use the DStDev function in a module to calculate the standard deviation across a set of students' test scores.

DStDev(expr, domain, [criteria]) DStDevP(expr, domain, [criteria]) The DStDev and DStDevP functions have the following arguments.

Argument Description

expr - An expression that identifies the numeric field on which you want to find the standard deviation. It can be a string expression identifying a field from a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DStDev or DStDevP function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DStDev and DStDevP functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DStDev and DStDevP functions will return a Null.


Remarks

If domain refers to fewer than two records or if fewer than two records satisfy criteria, the DStDev and DStDevP functions return a Null, indicating that a standard deviation can't be calculated.

Whether you use the DStDev or DStDevP function in a macro, module, query expression, or calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DStDev and DStDevP functions to specify criteria in the Criteria row of a select query. For example, you could create a query on a tblOrders table and a Products table to display all products for which the freight cost fell above the mean plus the standard deviation for freight cost. The Criteria row beneath the Shipping field would contain the following expression:

>(DStDev("[Shipping]", "tblOrders") + DAvg("[Shipping]", "tblOrders"))

You can use the DStDev and DStDevP functions in a calculated field expression of a query, or in the Update To row of an update query.

Note: You can use the DStDev and DStDevP functions or the StDev and StDevP functions in a calculated field expression of a totals query. If you use the DStDev or DStDevP function, values are calculated before data is grouped. If you use the StDev or StDevP function, the data is grouped before values in the field expression are evaluated.

Use the DStDev and DStDevP function in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display standard deviation for orders to be shipped to California, set the ControlSource property of a text box to the following expression:

=DStDev("[Shipping]", "tblOrders", "[ShipRegion] = 'CA'")

If you simply want to find the standard deviation across all records in domain, use the StDev or StDevP function.

Tip

If the data type of the field from which expr is derived is a number, the DStDev and DStDevP functions return a Double data type. If you use the DStDev or DStDevP function in a calculated control, include a data type conversion function in the expression to improve performance.

Note: Unsaved changes to records in domain are not included when you use these functions. If you want the DStDev or DStDevP function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following example returns estimates of the standard deviation for a population and a population sample for orders shipped to the United Kingdom. The domain is a tblOrders table. The criteria argument restricts the resulting set of records to those for which the ShipCountry value is UK.

CODE
Dim dblX As Double
Dim dblY As Double

' Sample estimate.
dblX = DStDev("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

' Population estimate.
dblY = DStDevP("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

The next example calculates the same estimates by using a variable, strCountry, in the criteria argument. Note that single quotation marks (') are included in the string expression, so that when the strings are concatenated, the string literal UK will be enclosed in single quotation marks.

CODE
Dim strCountry As String
Dim dblX As Double
Dim dblY As Double

strCountry = "UK"

dblX = DStDev("[Shipping]", "tblOrders", "[ShipCountry] = '" & strCountry & "'")

dblY = DStDevP("[Shipping]", "tblOrders", "[ShipCountry] = '" & strCountry & "'")


DSum

You can use the DSum functions to calculate the sum of a set of values in a specified set of records (a domain). Use the DSum function in Visual Basic, a macro, a query expression, or a calculated control.

For example, you could use the DSum function in a calculated field expression in a query to calculate the total sales made by a particular employee over a period of time. Or you could use the DSum function in a calculated control to display a running sum of sales for a particular product.

DSum(expr, domain, [criteria]) The DSum function has the following arguments.

Argument Description

expr - An expression that identifies the numeric field whose values you want to total. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

criteria - An optional string expression used to restrict the range of data on which the DSum function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.

Remarks

If no record satisfies the criteria argument or if domain contains no records, the DSum function returns a Null.

Whether you use the DSum function in a macro, module, query expression, or calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DSum function to specify criteria in the Criteria row of a query, in a calculated field in a query expression, or in the Update To row of an update query.

Note: You can use either the DSum or Sum function in a calculated field expression in a totals query. If you use the DSum function, values are calculated before data is grouped. If you use the Sum function, the data is grouped before values in the field expression are evaluated.

You may want to use the DSum function when you need to display the sum of a set of values from a field that is not in the record source for your form or report. For example, suppose you have a form that displays information about a particular product. You could use the DSum function to maintain a running total of sales of that product in a calculated control.

Tip

If you need to maintain a running total in a control on a report, you can use the RunningSum property of that control if the field on which it is based is included in the record source for the report. Use the DSum function to maintain a running sum on a form.

Note: Unsaved changes to records in domain aren't included when you use this function. If you want the DSum function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following example totals the values from the Shipping field for orders shipped to the United Kingdom. The domain is a tblOrders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.

CODE
Dim curX As Currency
curX = DSum("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

The next example calculates a total by using two separate criteria. Note that single quotation marks (') and number signs (#) are included in the string expression, so that when the strings are concatenated, the string literal will be enclosed in single quotation marks, and the date will be enclosed in number signs.

CODE
Dim curX As Currency
curX = DSum("[Shipping]", "tblOrders", _
   "[ShipCountry] = 'UK' AND [ShippedDate] > #1/1/2005#")

You can use a domain function in the Update To row of an update query. For example, suppose you want to track current sales by product in a Products table. You could add a new field called SalesSoFar to the Products table, and run an update query to calculate the correct values and update the records. Create a new query based on the Products table, and click Update on the Query menu. Add the SalesSoFar field to the query grid, and enter the following in the Update To row:

DSum("[Quantity]*[UnitPrice]", "tblOrderDetails", "[ProductID] = " & [ProductID])

When the query is run, Microsoft Access calculates the total amount of sales for each product, based on information from an Order Details table. The sum of sales for each product is added to the Products table.


DVar & DVarP

You can use the DVar and DVarP functions to estimate variance across a set of values in a specified set of records (a domain). Use the DVar and DVarP functions in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

Use the DVarP function to evaluate variance across a population and the DVar function to evaluate variance across a population sample.

For example, you could use the DVar function to calculate the variance across a set of students' test scores.

DVar(expr, domain, [criteria]) DVarP(expr, domain, [criteria]) The DVar and DVarP functions have the following arguments.

Argument Description

expr - An expression that identifies the numeric field on which you want to find the variance. It can be a string expression identifying a field from a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function. Any field included in expr must be a numeric field. domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter. criteria - An optional string expression used to restrict the range of data on which the DVar or DVarP function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DVar and DVarP functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DVar and DVarP functions return a Null.

Remarks

If domain refers to fewer than two records or if fewer than two records satisfy criteria, the DVar and DVarP functions return a Null, indicating that a variance can't be calculated.

Whether you use the DVar or DVarP function in a macro, module, query expression, or calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

You can use the DVar and DVarP function to specify criteria in the Criteria row of a select query, in a calculated field expression in a query, or in the Update To row of an update query.

Note You can use the DVar and DVarP functions or the Var and VarP functions in a calculated field expression in a totals query. If you use the DVar or DVarP function, values are calculated before data is grouped. If you use the Var or VarP function, the data is grouped before values in the field expression are evaluated.

Use the DVar and DVarP functions in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display a variance for orders to be shipped to California, set the ControlSource property of a text box to the following expression:

=DVar("[Shipping]", "tblOrders", "[ShipRegion] = 'CA'")

If you simply want to find the standard deviation across all records in domain, use the Var or VarP function.

Note: Unsaved changes to records in domain are not included when you use these functions. If you want the DVar or DVarP function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.

Example:

The following example returns estimates of the variance for a population and a population sample for orders shipped to the United Kingdom. The domain is a tblOrders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.

CODE
Dim dblX As Double
Dim dblY As Double

' Sample estimate.
dblX = DVar("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

' Population estimate.
dblY = DVarP("[Shipping]", "tblOrders", "[ShipCountry] = 'UK'")

The next example returns estimates by using a variable, strCountry, in the criteria argument. Note that single quotation marks (') are included in the string expression, so that when the strings are concatenated, the string literal UK will be enclosed in single quotation marks.

CODE
Dim strCountry As String|
Dim dblX As Double

strCountry = "UK"

dblX = DVar("[Shipping]", "tblOrders", "[ShipCountry] = '" & strCountry & "'")
Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 7,999 times.  This page was last modified 15:42, 30 August 2013 by Mark Davis.   Disclaimers