This example uses the Orders table to calculate the number of orders shipped to the United Kingdom. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query. The following example shows how you can limit the count to records in which either ShippedDate or Freight is not Null: SELECT Separate the field names with an ampersand (&). If all of the specified fields are Null, the record is not counted. If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. The following example calculates the number of records in the Orders table: SELECT Count(*) Do not enclose the asterisk in quotation marks (' '). Count( * ) is considerably faster than Count( ). If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. The Count function does not count records that have Null fields unless expr is the asterisk (*) wildcard character. It does not matter what values are stored in the records. For example, you could use Count to count the number of orders shipped to a particular country or region.Īlthough expr can perform a calculation on a field, Count simply tallies the number of records. You can use Count to count the number of records in an underlying query. You can count any kind of data, including text. I imported the table into a brand new database.
More info: - I used the Compact/Repair tool on the database and the problem didn't go away. But the query is still returning both rows when 'Unique Values' is set to YES. Operands in expr can include the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions). like that making the two rows distinct, I copied and pasted the value in one row to the other so they should be identical. The expr placeholder represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Calculates the number of records returned by a query.