MS Access Report Design Tip: How To Hide Duplicates (Selectively) In Your Access Reports

MS Access Report Design Tip: How To Hide Duplicates (Selectively) In Your Access Reports

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 Access VBA library) that can actually be applied directly to your Access report designs. The property in question is called ‘IsVisible‘!

access report design

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?

MS Access Report Design Tip: How To Hide Duplicates (Selectively) In Your Access Reports – The Steps

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). If you are unfamiliar with this process, please consider my eBook on how to build MS Access reports.

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 shown below:

access report design with query

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’ (as shown below).

access report design with hide duplicates

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 for both the Company Name and Order Date fields 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 the Company 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.

access report design final

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.

There are many approaches to using logic in controls for your Access report design which also extend into Conditional Formatting – a very powerful and programming free method indeed.

My eBooks on how to use Microsoft Access database covers the essential foundations and disciplines which all new database developers should learn – check them out!

This entry was posted in Microsoft Training, MS Access, Reports and tagged , , , , . Bookmark the permalink.

6 Responses to MS Access Report Design Tip: How To Hide Duplicates (Selectively) In Your Access Reports

  1. Pingback: Anonymous

  2. Ben Beitler says:

    CORRECTION!!!
    The above article example will not show the Order Date split as no order can be duplicated for the same day!
    It can happen for a wider (more related) set of related tables which will drill-down into its lower level and would require probably nested IIF functions to support the parent related field.
    This report only has the one key field (Order ID) but the principle is explained here as part of learning/education.

  3. Ben says:

    Yes, that’s correct.
    The illustration shows the Company Name that differs but this would also be the same for Order Date if the illustration contained same date transactions for the same customer.
    Thanks.

  4. Giorgio Rovelli says:

    Thanks Ben,
    “You need to have the Order ID set as Hide Duplicates so that Company Name and Order Date will respond to the IIF function and follow suite.” So what you’re saying is you retain the structure of the HideDuplicats report but add the IIF function to the Company Name control source?
    I was just comparing the two reports(HideDuplicates and IsVisible) in your picture and noticing the only difference is in the Company Name column, everything else remais the same.

  5. Ben says:

    You need to have the Order ID set as Hide Duplicates so that Company Name and Order Date will respond to the IIF function and follow suite.
    I thought it would be obvious based on the controls function and how it calculates – perhaps it wasn’t!

  6. Giorgio Rovelli says:

    “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).”
    But the same thing also occurs with the Hide Duplicates report.
    “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.”
    I see the same amount of blank values for the order date field in both the Hide Duplicates and IsVisible reports.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.