Microsoft Access Tips for Casual Users

Provided by Allen Browne, October 2006

Hide duplicates selectively

This article explains how to use the IsVisible property in conjunction with HideDuplicates to selectively hide repeating values on a report.

Relational databases are full of one-to-many relations. In Northwind, one Order can have many Order Details. So, in queries and reports, fields from the "One" side of the relation repeat on every row like this:

The HideDuplicates property (on the Format tab of the Properties sheet) helps. Setting HideDuplicates to Yes for OrderID, OrderDate, and CompanyName, gives a more readable report, but is not quite right:

The Date and Company for Order 10617 disappeared, since they were the same the previous order. Similarly, the company name is hidden in order 10619. How can we suppress the date and company only when repeating the same order, but show them for a new order even if they are the same as the previous row?

When Access hides duplicates, it sets a special property named IsVisible. By testing the IsVisible property of the OrderID, we can hide the OrderDate and CompanyName only when the OrderID changes.

Set the properties of the OrderID text box like this:

Control Source . . .	=IIf(OrderID.IsVisible,[OrderDate],Null)
Hide Duplicates . . .	No
Name . . . . . . . . .	txtOrderDate

The Control Source tests the IsVisible property of the OrderID. If it is visible, then the control shows the OrderDate. If it is not visible, it shows Null. Leave the HideDuplicates property turned off. We must change the name as well, because Access gets confused if a control has the same name as a field, but is bound to something else.

Similarly, set the ControlSource of the CompanyName text box to:
        =IIf(OrderID.IsVisible,[CompanyName],Null)
and change its name to (say) txtCompanyName.

Now the report looks like this:

Note that the IsVisible property is not the same as the Visible property in the Properties box. IsVisible is not available at design time. Access sets it for you when the report runs, for exactly the purpose explained in this article.

If you are trying to create the sample report above in the Northwind sample database, here is the query it is based on:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) INNER JOIN [Order Details]
  ON Orders.OrderID=[Order Details].OrderID) ON Products.ProductID=[Order Details].ProductID
WHERE Orders.OrderID > 10613
ORDER BY Orders.OrderID;

In summary, use HideDuplicates where you do want duplicates hidden, but for other controls that should hide at the same time, test the IsVisible property in their ControlSource.