+ Reply to Thread
Results 1 to 10 of 10

Code to create a report without zero values

  1. #1
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Code to create a report without zero values

    Hi,

    I have the following code in Access database.It creates a pdf file with YTD and Quarterly numbers. If the Quarterly value is zero, I would like to create a report where it does not show up that quarter number. What can I add to the following code see only non zero numbers.

    Thanks,
    Deenah


    Please Login or Register  to view this content.
    Last edited by Deenah; 11-08-2011 at 05:12 PM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Code to create a report without zero values

    You also posted an Access question in an Excel forum. I will move it for you this time

  3. #3
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Code to create a report without zero values

    Sorry I am new at the forum. I will read the rules and comply going forward.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to create a report without zero values

    You posted way too much code to decipher. Can you just post the single SQL statement that is bringing in the zeros you don't want to see?

    In general, you can specify in the WHERE clause that you want to exclude results = 0. It'd be something like:

    SELECT * FROM table WHERE QuarterlyData <> 0
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  5. #5
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Code to create a report without zero values

    Hi Dave,

    Thanks for your reply. Can you please tell me where I should put the Where code. I am reposting the code where I need to create a statement without zero amounts.


    Please Login or Register  to view this content.
    Thanks,
    deenah

  6. #6
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to create a report without zero values

    deenah, as I said before there is WAY too much code there to make sense of any of it. If you don't understand your code, you need to find a way to learn what it is doing. You have several SQL statements there, and there is no way I can know which one(s) need to be changed.

    If you can post your db I may be able to figure it out, but otherwise I don't think I can help you.

  7. #7
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Code to create a report without zero values

    I am sorry. But I think I need to make a change somewhere in the following code. where there is WHERE command.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Code to create a report without zero values

    When yuo are using aggregate functions, the HAVING takes the place of the WHERE clause. So for those three statements, add something like
    AND Statement_Quarter <> 0.

    Additionally, if this is being displayed on a report you can use conditional formatting to hide any zero values.

  9. #9
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Code to create a report without zero values

    Thanks Dave. I figured out where I had to make the change.

    Regards,
    Deenah

  10. #10
    Registered User
    Join Date
    10-26-2011
    Location
    California,USA
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Code to create a report without zero values

    Thanks again Dave for the solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1