How do I reference the detail section of an access report to check if itis empty

Posted on 2011-04-28
Last Modified: 2012-06-21
I have a report that breaks on client. If there is no detail information for a client I want to display a text box that says "No information". I have the text box hidden and will change it to visible based on the trigger.
Problem - I have tried .HASDATA but it always returns true even if the detail section is empty.
I have dozens of clients and they are populated 95% of thie time. This will put the finishing touches on the report.
Question by:nickmark
    LVL 39

    Expert Comment

    Try to use report event - On No Data and there set your control to visible

    Author Comment

    The report will always have data but some of the detail sections will not. For instance there will be data for 40 clients but no data for one. Can I put this on the report level or do I need something on the detail level. This report breaks on Ckient.
    LVL 39

    Expert Comment

    Is client presented in source query if there are no data records? May be for your case is better to use subreports, where you can analyze - are there data or not
    LVL 77

    Accepted Solution

    I assume you must be using a left join to include all clients in your report and that there is a group based on clientid or similar.

    In the group header print event procedure test for a sum value that should be non-zero if there are records to print.  You can hide or display your message depending on the result..

    If sum(shippingfee) = 0 Then
    Me.LabelNoData.Visible = True
    Me.LabelNoData.Visible = False
    End If

    You must use a Sum().
    You cannot use a COUNT() function because there must be (at least) one record present to create the group header, so the Count() will never be zero.


    Author Comment

    Thanks - I will modify the report. I appreciate your help!

    Author Closing Comment

    Thanks - That is exactly what I ended up with as my solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now