Identifying the true symptoms may viagra cipla 20mg help you knowing more about each: Kamagra tablets: These are widely accepted effective treatment available in three different strengths of dosages. Not necessarily, it would have to be a medicine for treating hypertension and cardiovascular diseases, until researches did not find it helpful for erection production. buying levitra online This is not something which occurs to a lot of men discounts on viagra across the world. A shame-based family system buying levitra online is characterized by the parent’s use of shame to provide direction.
Use DCount() Function in MS Access
DCount function is a built-in function to get the number of records from the set of records or data from table, query, SQL, or Recordset. We can use the DCount function in Visual Basic and macro.
DCount Syntax
DCount(Expression, Domain, Criteria [optional])
- Expression identifies the field that you want to count records. It can be a string expression identifying a filed in a table or query.
- Domain is a set of records. It can be a table name or a query name for a query does not require a parameter.
- Criteria is an optional expression to restrict the range of data. Criteria is equivalent to the WHERE clause in an SQL Expression, but without the word WHERE.
Example of Using DCount Function
#1 Count All
Use DCount function to count a specific field in a Domain. For example below, use Dcount to count all customers from tbl_Customer table. The result will be total records in tbl_Customer table. I have 10 customers in this table then the result will be 10 after clicking a button Get Result on a test form shown below.
Code:
Private Sub Command0_Click()
Dim IntX As Integer
IntX = DCount("Customer_ID", "tbl_customer")
Me.txtResult = IntX
End Sub
#2 Count with Criteria
Use DCount function to count a specific field with the criteria. For example, use Dcount to count customers who live in CA from tbl_Customer table. The result will be 7 after clicking a button Get Result as shown below.
Code:
Private Sub Command0_Click()
Dim IntX As Integer
IntX = DCount("Customer_ID", "tbl_customer", "state = 'CA'")
Me.txtResult = IntX
End Sub
#3 Count Null
DCount function does not count records that contain Null values in the field referenced by Expression. So we use the asterisk (*) wildcard character in the Expression. For example, use Dcount to count all records in the tbl_Customer table including a blank record. Sometime we want all records as a result. The result will be 10 including a blank record as shown below.
Code:
Private Sub Command0_Click()
Dim IntX As Integer
IntX = DCount("*", "tbl_customer")
Me.txtResult = IntX
End Sub
#4 Count Multiple fields
We can use multiple fields for the Expression as a part of DCount function. A concatenation operator, either an ampersand (&) or the addition operator (+) can be used to separate the field name for the Expression. The result will be different from using & and + operator.
Expression Using Ampersand Operator (&)
When using the ampersand to separate the fields, the DCount function will return the number of records that containing data in any of the listed fields. It will not count when both fields are null or blank. Per the customer list above, the customer_ID 5 has a blank state, customer_ID 11 has a blank customer name, and customer_ID 14 has a blank data on both customer name and state field. The result of using the ampersand (&) in the expression will be 9 since only the Customer_ID 14 has null data on both CustomerName and State fields.
Code:
IntX = DCount(“[CustomerName] & [State]”, “tbl_customer”)
Me.txtResult = IntX
Expression Using Addition Operator (+)
When using the Addition Operator (+) to separate the fields, the DCount function will count only the record that does not contain any field is null or blank. Per the customer list above, there are three records that have a null or blank field for customer name and state fields so the result will be 7.
Code:
IntX = DCount(“[CustomerName] + [State]”, “tbl_customer”)
Me.txtResult = IntX
#5 Create Count Function with Dcount
The DCount function also can be used as part of the count function. The function below is the example of how to use DCount function inside the function. This function needs only to enter a parameter for a state then get a number of records from tbl_Customer table. This function can be put under the current form or module.
Code:
Public Function GetCount(strState As String) As Integer
GetCount = DCount("[Customer_ID]", "tbl_Customer", "[State] = '" & strState & "'")
End Function
Call GetCount Function
The result of GetCount function from the input of “CA” will be assign to a variable intX. The result will be 6 records for the criteria State = “CA” as shown below.
Private Sub Command0_Click()
Dim IntX As Integer
IntX = GetCount("CA")
Me.txtResult = IntX
End Sub
