One of the many tips within the access database design vault are normally hidden secrets of unknown properties. For your Access report design collection of properties, you may have noticed a wealth of useful attributes to choose and one in particular I want to highlight is the ‘Hide Duplicates‘ property for reports.
However, this property is not perfect as I will explain in a moment. Instead, you may want to use another hidden property (outside of the Acess VBA library) that can actually be applied directly to your Access report designs. The property in question is called ‘IsVisible‘!
From the illustration above, I’m showing three different instances of the same report. The first is a normal typical list report of customers, the order date, order number and products for each order. The second instance shows the ‘Hide Duplicates‘ property applied to the first three controls which suppresses repeating values but is independent to any other field (or control). The third report however is using the more flexible ‘IsVisible‘ property which is tested across other fields (or controls).
So how do we create this Access report design?
1. Create your Access report in the normal manner (usually backed by a query of course) and make this report a simple tabular list view using either the template or wizard (whichever version you use).
2. Switch to the design view mode for your new report. In my example, I have a simple list report of UK based customers with an order date, number and product information as listed below:
| Company Name | Order Date | Order ID | Product | Qty | Price |
3. To use the ‘Hide Duplicates‘ property, select the first three controls in this report (Company Name, Order Date and Order ID) and display the Property Sheet pane (or Properties window for earlier versions).
In the ‘Format‘ tab, scroll down and look for this property and set it ‘Yes’
When you preview this report, you will see the ‘Company Name‘ not being repeated until there is a change in the customer (value) but the order date and number will also change on each instance leaving a fragmented view of the three controls working together. This could also create too many blank values for the order date field if there were more than one order for the same customer with the same date.
4. Now switch back to design view mode and remove (set it to ‘No’) the ‘Hide Duplicates‘ property as it is no longer required here.
How about a quick save before the next bit?
5. This is where we are going to introduce a hidden property called ‘IsVisible‘ which returns a logical True or False value for control with a value.
Logically, we would like to only show the Company Name and Order Date when there is a genuine change with the Order ID field (since this is unique for each order but not to the product items).
Therefore, we need to use an expression to logically test for this scenario and modify both theCompany Name and Order Date controls to look out for a change in value to the Order ID.
In design view mode, select the first control (Company Name) and locate the ‘Control Source‘ property (via the ‘Data‘ tab) and remove the field reference. Now type or use the Expression Builder tool the following in its place:
=IIf([Order ID].IsVisible,[Company Name],Null)
Repeat this for ‘Order Date’ making sure you replace the obvious [Company Name] with[Order Date].
Also, you will need to rename the two modified controls so they do not conflict with the field reference. I have renamed then as txtCompanyName and txtOrderDate but you can use any unique name to be honest.
6. I recommend saving your changes first and then go and preview this report.
Make sure you actually use Print Preview mode and not the Layout View option (which is available to the later versions).
There you have it, a clean list of records which is an alternative way to using too many group sections for your reports.
