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

ID: Q237378

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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;

Additional query words: pra

Keywords : kbdta QryProb
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug