February 5th, 2013, 11:18 AM
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!
February 5th, 2013, 03:04 PM
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.
February 6th, 2013, 04:25 AM
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.
February 6th, 2013, 06:43 AM
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.
February 6th, 2013, 08:53 AM
Here's the link:
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!
February 6th, 2013, 10:05 AM
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.
February 6th, 2013, 10:37 AM
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?
February 6th, 2013, 02:36 PM
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.
February 21st, 2013, 06:02 AM
simply dropping by to say hello
February 21st, 2013, 06:09 AM
just stopping by to say hey