Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

The domain functions explained

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 12:48 pm    Post subject: The domain functions explained Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group