Microsoft Access Office VB VBA Help and Examples  Microsoft Access Help      Search      Privacy      Contact    
 HOME     MS ACCESS     MS OUTLOOK     MS EXCEL     VB and VBA     FREE PROGRAMS     iPAQ     PERSONAL 
 ACCESS     GENERAL     QUERIES     FORMS     REPORTS     MODULES     RANTS     TUTORIALS 

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]")

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.