Microsoft KB Archive/237378

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

Article ID: 237378

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q237378





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 that this is a problem in the Microsoft products that are 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: kbbug kbfix KB237378

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.