Results 1 to 6 of 6
  1. #1
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85

    Suppressing printing of a record in a report if a certain field contains a zero value

    I realize that I could suppress a given record in which a specific field contains a zero value if I used a view


    for the report. However, I have a situation in which I want to use a table instead. I would like to
    be able to handle this in the report setup so that any record in the CumTally field with a 0 value
    would be completely suppressed on the report. Might be more complicated, but there's probably
    a way to do it with VBA in the right place.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have yet to come across a need for a form or report to be based on a table, and if you used a query instead, this would be far simpler. I'd be interested to know why you believe you cannot use a query, which by its very nature can be made to fully replicate any table. In addition it would give you the result you want because you'd filter out records based on your value in the first place.

    You might find that a format event (detail.format?) would work but it's not something I can imagine trying in place of a query for the report recordsource so I don't know for sure.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Well, so I gave it a try with a query. I called it qrySnacksTallyCombined. Here's the code
    SELECT*
    FROM tblSnacksTallyNorthMachines
    WHERE CumTally >0
    UNION
    SELECT*
    FROM tblSnacksTallyNorthStore
    WHERE CumTally >0
    UNION
    SELECT*
    FROM tblSnacksTallySouthMachines
    WHERE CumTally >0
    UNION
    SELECT*
    FROM tblSnacksTallySouthStore
    WHERE CumTally >0
    ORDER BY LOCATION

    When I feed this query to the report form ALL records in the various tables come out in the report, although some items have a zero CumTally.
    What am I doing wrong?


  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Without seeing db, can only guess. You get this result when you run this as a query or only in the report, or both? I'm thinking just the report.
    Is CumTally a calculated query field, a calculated table field or a stored calculation in a table field?

    Looks to me like your tables might not be properly normalized i.e. why not one table with a location field?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Oops! My new query IS working just right. The reason I thought it wasn't that I didn't have any data
    in a couple of the source tables. So all is well. The zero value items are not printing, the others are.
    Thread solved, except that for the life of me I can't find that damned button to click that says it's solved.
    Why shouldn't this be one of the buttons in this quick reply area?

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think it's in the thread tools drop-down?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-23-2018, 10:43 AM
  2. Suppressing portions of a report
    By GraeagleBill in forum Reports
    Replies: 16
    Last Post: 03-02-2017, 02:39 PM
  3. Report printing a single record
    By gebmiller1984 in forum Access
    Replies: 5
    Last Post: 01-15-2015, 03:47 PM
  4. Suppressing records in a report
    By snowboarder234 in forum Reports
    Replies: 1
    Last Post: 07-11-2012, 07:52 PM
  5. Replies: 0
    Last Post: 05-25-2011, 06:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums