#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Hide zero value fields in form or report, Help!


    I've been using Access for a while, but I am entirely self taught, and I've hit a problem I can't figure out.

    I have a table with about 100 columns, which has about a thousand rows in it, which the user can narrow down with a query to a specific date, leaving them with only 30 rows or so. When this is done however, many of the columns are filled entirely with zeros, which the user doesn't want to have to print out when the print out the selection they've made. Is there any way that these fields can be hidden on either a pivot table, or a report, leaving them with only the data that they need, which they can then print out?

    Thanks a lot!
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Not sure about pivot reports, I don't really use them, but in a report instead of simply using the field name as the record source for a text box you can use:
    =iif([YourFieldNameHere] = 0,null,[YourFieldNameHere])
    If your doing further calculations in you report don't forget to include the nz function to handle your new null values.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    That doesn't quite work, because the user wants to be able to print the result as a grid, which just misses out all the columns which contain only zeros. When I do what you said, the results is just #TYPE! in all of the boxes.

    I also have the sum of the columns, which again the non-zero ones should be displayed if that helps.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    That should work, no reason it shouldn't. Can you post your Database to a file share site so we can take a look. (Strip any confidential information and compact and repair it first.) Without seeing what you are doing there's really no way to know what the problem is at this point.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Here's the link:

    https://www.dropbox.com/s/2x822pe9cv...Database.accdb

    I'm trying to display all of the information from the Production table where Finish is between two user specified dates, ignoring any of the three letter codes (EBW, EBD etc) where there are only zeros in that column, and then make it so that all that data can be printed out as a table. My attempt at doing what you suggested is in the report called Tabular Report Query.

    Thanks a lot for trying to help me!
  10. #6
  11. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Adding the formulat to the recordsource has created a circular references. I usually do it from unbound textboxes which is why I didn't consider the possibility earlier. All you need to do is change the name of your textbox from EBD to something else (I used EBD2) and it works as it should.

    After seeing what you have, I do have to question the setup used here. If I'm reading this correctly you listed every possible part/component/assembly/product whatever the letter codes stand for on each and every record. Not only is this time consuming but the data is not normalized as it should be. I think the database would be more functional if these were broken down into seperate tables. With your current method it would be extremely difficult to modify or add any of your letter coded columns without major reprocussions throughout the db. Consider the following instead.

    Production table - include basic production info such as is, batch, order#, start, finish, or any other information specific to this one specific production record in your table.

    Component/part table - this is where you list all of your letter codes (vertically) with maybe a description, cost, quantity on hand, or whatever infomation is specific that you might want to store. it would look something like this:

    Code : Description : Cost : Vendor : Quantity
    EBD : Handle : 3.25 : ACME Co. : 670
    ECT : Cotter Pin : 0.15 : HomeDepot : 3000

    (I know this may be incorrect as far as it relates to your data, but the principle remains the same.

    Final table relates your production table to your parts table
    ID : ProductionID : Code : quantity

    1 : F28 : EES : 1
    2 : F28 : EPM : 36


    Now on your reports you only have to list parts that are actually used on the individual order and you don't have to worry about 50 columns that may contain zero's.
    I fear if you continue on the path you're on you may find it difficult to ever achieve the desired end result of your database.
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    That seems to have worked, except that the label still there, so it's left a gap for the data that might be there, and not formatted it the best way; is there a way to make the labels disappear with the text boxes?

    I can see your point, that the database is not made the best way, especially if you start to delve into it at all. The problem I have is that it was made about six years ago, and I've just been tasked with updating some of the methods now a new QA manager has taken over and wants it to do more. I had considered changing the whole database, but I was afraid of losing any of the data which is already there, because, although it is in an ugly format, it is important data. Many of the tasks it does, it does in a hacky unfriendly way, but they work, and they don't want to lose functionality or data. If I were to change the database like that, can you see any way that I could do it, without losing the data which is already in it?
  14. #8
  15. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    You may want to look into using a query to gather your information before you put it into report form. With your current setup is very difficult (I don't want to say impossible, but pretty close) to get the type of layout that you're looking for. The way your report is structured, to completely eliminate a column isn't going to work.
  16. #9
  17. No Profile Picture
    Registered User

    Join Date
    Mar 2010
    Location
    United States
    Posts
    11
    Rep Power
    0

    hey everyone


    simply dropping by to say hello
  18. #10
  19. No Profile Picture
    Registered User

    Join Date
    Mar 2010
    Location
    United States
    Posts
    11
    Rep Power
    0

    hey everyone


    just stopping by to say hey

Similar Threads

  1. Replies: 1
    Last Post: May 17th, 2007, 10:50 PM
  2. Max no of fields in form & report
    By mrszzz in forum Microsoft Access Help
    Replies: 6
    Last Post: August 17th, 2006, 11:17 AM
  3. Create report based on form fields
    By Bato in forum Visual Basic Programming
    Replies: 0
    Last Post: January 26th, 2006, 02:24 PM
  4. Hide fields on a form dependant on contents of txt box?
    By KennyNotts in forum Microsoft Access Help
    Replies: 5
    Last Post: May 19th, 2005, 03:37 PM
  5. Form Fields - Hide Initial Value
    By microimage in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: February 6th, 2004, 10:12 PM

IMN logo majestic logo threadwatch logo seochat tools logo