ACC2000: Grouping by a Domain Function in a Totals Query Returns Meaningless Data

When you use a domain function in a totals query and set the Total row to Group By, the domain function may return meaningless data.

When you set a field in a totals query to Group By, the data returned is of an unknown data type. Microsoft Access 2000 maps unknown data types to the Binary data type. The output of the query is the representation of that binary output.

You can wrap the domain function in one of the conversions functions to return the correct data type. For example, you could convert the value returned to an integer by using either the CInt() or CLng() functions.

MyTotal: CLng(DCount("OrderID", "Orders", "[EmployeeID] = " & EmployeeID"))
				

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This behavior occurs when a domain function returns a number. This behavior does not occur when you use domain functions on strings.

In many cases, you can avoid the behavior described in the "Symptoms" section by obtaining the results of the domain function through other means, such as by using the Count() aggregate function in the Total row of the totals query, or by using a standard select query instead of a totals query. For example, the following SQL statement causes the problem behavior:

SELECT Employees.EmployeeID, DCount("OrderID", "Orders", "[EmployeeID] = " & EmployeeID) As MyTotal
FROM Employees
GROUP BY Employees.EmployeeID, DCount("OrderID", "Orders", "[EmployeeID] = " & EmployeeID);
				

However, the following is a valid SQL statement that returns each EmployeeID from the Employees table and a count of the number of orders each employee has taken. This query uses the Count() aggregate function to completely avoid the DCount() function.

SELECT Orders.EmployeeID, Count(Orders.OrderID) As MyTotal
FROM Orders
GROUP BY Orders.EmployeeID;
				

You can get the same results by using the DCount() function in the query, but by not using a totals query.

SELECT Employees.EmployeeID, DCount("OrderID", "Orders", "[EmployeeID] = " & EmployeeID) As MyTotal
FROM Employees;