BIGmiralli Frequent Poster
Joined: 17 Apr 2007 Posts: 38 Location: Boston, Massachusetts
|
Posted: Fri May 23, 2008 12:48 pm Post subject: The domain functions explained |
|
|
Four domain functions are available in access to allow you to perform a count, sum, average or lookup a record easily. These four functions are Dcount, DSum, Davg,Dlookup. Please note the implementation of these is not very quick but generally it will be slightly faster than opening a recrodset yourself and performing the calculation.
--------------------------------------------------------------------------------
DCount counts the number of records that meet criteria specified in the criteria:
DCount("FieldToCount","Table or Query Name","Criteria")
Examples: The first example counts the number of records in the customer table that have a conatact name greater than S. The second counts contact names less than S and the third counts contact names that have S as the first letter.
DCount("[ContactName]","Customers","[ContactName] > 'S'")
DCount("[ContactName]","Customers","[ContactName] < 'S'")
DCount("[ContactName]","Customers","[ContactName] Like 'S*'")
--------------------------------------------------------------------------------
DSum sums the field of records that meet criteria specified in the criteria:
DSum("FieldToSum or Expr","Table or Query Name","Criteria")
Examples: The first example sums all the InvoiceTotals in the CustomerInvoices Table. The second example sums all the InvoiceTotals in the CustomerInvoices Table that have an invoice date greater than 21st Febuary 2000. The third example shows how you can perform a calculated sum, this particular one takes the InvoiceSubTotal multiplies by CityTaxCode and then sums all those for each record.
DSum("[InvoiceTotal]","CustomersInvoices")
DSum("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] > #21/1/2000#")
DSum("[InvoiceSubTotal] * [CityTaxCode]","CustomersInvoices")
--------------------------------------------------------------------------------
DAvg gives the average of all records that meet criteria specified in the criteria:
DSum("FieldToAverage or Expr","Table or Query Name","Criteria")
Examples: The first example gives the average of all the InvoiceTotals in the CustomerInvoices Table. The second example gives the average of all the InvoiceTotals in the CustomerInvoices Table that have an invoice date greater than 21st Febuary 2000.
DAvg("[InvoiceTotal]","CustomersInvoices")
DAvg("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] > #21/1/2000#")
--------------------------------------------------------------------------------
DLookup returns the field specified of first record that meet criteria specified in the criteria (If their is no match it returns a Null):
DLookUp("FieldToSum","Table or Query Name","Criteria")
Examples: The first example returns the first InvoiceTotal in the CustomerInvoices table. The second example returns the first InvoiceTotal from customerinvoices where the invoice date is equal to 21st Febuary and the customerid is equal to 7. The third example is the most common use of this function, returning the customer name given their CustomerID on the customer invoice form. It saves you having to do sub forms.
DLookUp("[InvoiceTotal]","CustomersInvoices")
DLookUp("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] = #21/1/2000# AND [CustomerID] = 7")
DLookUp("[CustomerName]","Customers","[CustomerID] = [Forms]![CustomerInvoice]![CustomerID]")
________
MAKING FRIED FOOD |
|