Hiding Entire Columns in Report when value = 0

[tennessee]

Hi all,

I have been searching for a couple days on this. I need to hide a column in the reports when all values within that column are "0" or blank.
Additionally if the columns that have values in them can shift so that the table does not have any spaces in between, that would be preferrable.

Things I have tried:

In the Control Source of the Text box I have input:
=IIf([WOOD_SF]="0",WOOD_SF.Visible="false",WOOD_SF.Visible="true ")
=IIf([WOOD_SF]="0",WOOD_SF.hidden="true",WOOD_SF.hidden="fals e")
I received either an "enter a parameter value" message or it would hide the whole column even if there was a value greater than 0 in on of the fields.

In the visual Basic Editor I have tried:
1.
/CODE/Private Sub Report_Open(Cancel As Integer)
If LenWOOD_SF > 0 Then
WOOD_SF.Visible = True
Else
WOOD_SF.Visible = False
End If
End Sub/CODE/
2.
/CODE/Me!WOOD_SF.Visible=(Me!WOOD_SF)/CODE/

I am kind of new to Visual Basic so I would prefer the easiest way possible. Working off MS ACCESS 2003, WindowsXP if that helps.

Thanks for any help you can provide.

[puppydogbuddy]

Hi all,

I have been searching for a couple days on this. I need to hide a column in the reports when all values within that column are "0" or blank.
Additionally if the columns that have values in them can shift so that the table does not have any spaces in between, that would be preferrable.

Things I have tried:

In the Control Source of the Text box I have input:
=IIf([WOOD_SF]="0",WOOD_SF.Visible="false",WOOD_SF.Visible="true ")
=IIf([WOOD_SF]="0",WOOD_SF.hidden="true",WOOD_SF.hidden="fals e")
I received either an "enter a parameter value" message or it would hide the whole column even if there was a value greater than 0 in on of the fields.

In the visual Basic Editor I have tried:
1.
/CODE/Private Sub Report_Open(Cancel As Integer)
If LenWOOD_SF > 0 Then
WOOD_SF.Visible = True
Else
WOOD_SF.Visible = False
End If
End Sub/CODE/
2.
/CODE/Me!WOOD_SF.Visible=(Me!WOOD_SF)/CODE/

I am kind of new to Visual Basic so I would prefer the easiest way possible. Working off MS ACCESS 2003, WindowsXP if that helps.

Thanks for any help you can provide.

The easiest way to accomplish what you want is to put the constraint in the query that is the record source for the report. Go to the criteria row of the column in the query grid that has the zero/null balances and put the following expression:
<>0 Or <>nz([yourColumnName],0)

Replace yourColumnName in the expression with the actual column name

[tennessee]

The easiest way to accomplish what you want is to put the constraint in the query that is the record source for the report. Go to the criteria row of the column in the query grid that has the zero/null balances and put the following expression:
<>0 Or <>nz([yourColumnName],0)

Replace yourColumnName in the expression with the actual column name

That didnt work out for me.. It took out all my values from my report. In including the column values that I want to show. What am I doing wrong?

[puppydogbuddy]

That didnt work out for me.. It took out all my values from my report. In including the column values that I want to show. What am I doing wrong?

I need more info. Please post the sql from the sql view of your query.

[tennessee]

I need more info. Please post the sql from the sql view of your query.

Here is what I got... Is this what you need?

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>0 Or ([ESTIMATING SHEET].CPT_SY)<>nz([CPT_SY],0)) AND (([ESTIMATING SHEET].TILE_SF)<>0 Or ([ESTIMATING SHEET].TILE_SF)<>nz([TILE_SF],0)) AND (([ESTIMATING SHEET].VINYL_SY)<>0 Or ([ESTIMATING SHEET].VINYL_SY)<>nz([VINYL_SY],0)) AND (([ESTIMATING SHEET].WOOD_SF)<>0 Or ([ESTIMATING SHEET].WOOD_SF)<>nz([WOOD_SF],0)) AND (([ESTIMATING SHEET].STONE_SF)<>0 Or ([ESTIMATING SHEET].STONE_SF)<>nz([STONE_SF],0)));

[puppydogbuddy]

Here is what I got... Is this what you need?

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>0 Or ([ESTIMATING SHEET].CPT_SY)<>nz([CPT_SY],0)) AND (([ESTIMATING SHEET].TILE_SF)<>0 Or ([ESTIMATING SHEET].TILE_SF)<>nz([TILE_SF],0)) AND (([ESTIMATING SHEET].VINYL_SY)<>0 Or ([ESTIMATING SHEET].VINYL_SY)<>nz([VINYL_SY],0)) AND (([ESTIMATING SHEET].WOOD_SF)<>0 Or ([ESTIMATING SHEET].WOOD_SF)<>nz([WOOD_SF],0)) AND (([ESTIMATING SHEET].STONE_SF)<>0 Or ([ESTIMATING SHEET].STONE_SF)<>nz([STONE_SF],0)));

OK, from the above, it looks like you are applying the criteria to multiple columns using the same criteria row. if you apply to more than one column, the criteria for the first column should go into criteria row 1, the second into criteria row 2.....and so on.

[puppydogbuddy]

also, in your case if you are only having a problem with 0 balances, you can simplify the expression to: <>0

[tennessee]

OK, from the above, it looks like you are applying the criteria to multiple columns using the same criteria row. if you apply to more than one column, the criteria for the first column should go into criteria row 1, the second into criteria row 2.....and so on.

My values on my report came back. However I am back to my original issue. The formula is not hiding the columns if the value is zero..

Here is the SQL view again..

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>0 Or ([ESTIMATING SHEET].CPT_SY)<>nz([CPT_SY],0))) OR ((([ESTIMATING SHEET].TILE_SF)<>0 Or ([ESTIMATING SHEET].TILE_SF)<>nz([TILE_SF],0))) OR ((([ESTIMATING SHEET].VINYL_SY)<>0 Or ([ESTIMATING SHEET].VINYL_SY)<>nz([VINYL_SY],0))) OR ((([ESTIMATING SHEET].WOOD_SF)<>0 Or ([ESTIMATING SHEET].WOOD_SF)<>nz([WOOD_SF],0))) OR ((([ESTIMATING SHEET].STONE_SF)<>0 Or ([ESTIMATING SHEET].STONE_SF)<>nz([STONE_SF],0)));

[tennessee]

also, in your case if you are only having a problem with 0 balances, you can simplify the expression to: <>0

Tried this too and the columns in my report are still there showing zeros..

[puppydogbuddy]

My values on my report came back. However I am back to my original issue. The formula is not hiding the columns if the value is zero..

Here is the SQL view again..

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>0 Or ([ESTIMATING SHEET].CPT_SY)<>nz([CPT_SY],0))) OR ((([ESTIMATING SHEET].TILE_SF)<>0 Or ([ESTIMATING SHEET].TILE_SF)<>nz([TILE_SF],0))) OR ((([ESTIMATING SHEET].VINYL_SY)<>0 Or ([ESTIMATING SHEET].VINYL_SY)<>nz([VINYL_SY],0))) OR ((([ESTIMATING SHEET].WOOD_SF)<>0 Or ([ESTIMATING SHEET].WOOD_SF)<>nz([WOOD_SF],0))) OR ((([ESTIMATING SHEET].STONE_SF)<>0 Or ([ESTIMATING SHEET].STONE_SF)<>nz([STONE_SF],0)));

Ok, lets do the following:
1. take out the nz component of the expression and simplify the expression to: <>0, then test....any change?
2. If #1 above is no change, then take out all <> 0 criteria, and start over, adding the criteria to one column at a time..and test after each column is added. Tell me what happens.

[tennessee]

Ok, lets do the following:
1. take out the nz component of the expression and simplify the expression to: <>0, then test....any change?
2. If #1 above is no change, then take out all <> 0 criteria, and start over, adding the criteria to one column at a time..and test after each column is added. Tell me what happens.

Ok.. I think I know whats wrong. I placed the expression in one at a time. Those with zero value columns took away data out of the rows not columns.

So I looked at my report Properties and the column layout is: Across, then Down. How do I change this to Down then Across? Am I right on that?

[puppydogbuddy]

Ok.. I think I know whats wrong. I placed the expression in one at a time. Those with zero value columns took away data out of the rows not columns.

So I looked at my report Properties and the column layout is: Across, then Down. How do I change this to Down then Across? Am I right on that?

You could be... I am not sure...I have not tested on different layouts.

to change layout:
1. place report in design view
2. go to command menu, select File>page setup>column tab

[tennessee]

You could be... I am not sure...I have not tested on different layouts.

to change layout:
1. place report in design view
2. go to command menu, select File>page setup>column tab

No that didnt work.. It only gives me one ROW on the report and all the fields are empty..

[puppydogbuddy]

No that didnt work.. It only gives me one ROW on the report and all the fields are empty..

I did not think the layout had anything to do with it. Try putting your criteria this way to see if it excludes the zero values in all rows. Go to the first column of your where clause and put <> 0 in the first criteria row; then go to next column and put <> 0 in the criteria row and also repeat the <> 0 of the previous column....and so on as shown below. Let me know if that worked.

-------------------------------col7---------col8-----------col9-------col10 and so on.....
criteria row1------------<>0

criteria row2------------<>0----------<>0

criteria row3------------<>0----------<>0------------<>0

criteria row4------------<>0----------<>0------------<>0----------<>0

[tennessee]

I did not think the layout had anything to do with it. Try putting your criteria this way to see if it excludes the zero values in all rows. Go to the first column of your where clause and put <> 0 in the first criteria row; then go to next column and put <> 0 in the criteria row and also repeat the <> 0 of the previous column....and so on as shown below. Let me know if that worked.

-------------------------------col7---------col8-----------col9-------col10 and so on.....
criteria row1------------<>0

criteria row2------------<>0----------<>0

criteria row3------------<>0----------<>0------------<>0

criteria row4------------<>0----------<>0------------<>0----------<>0

All of the values show. On the columns where there are only "0" values, it did not delete the column or remove the zeros..

[puppydogbuddy]

All of the values show. On the columns where there are only "0" values, it did not delete the column or remove the zeros..

Ok, I think I figured out the problem. I went back to queries that I have used the expression that I gave you in my original response:

<>0 Or <>nz([YourColumnName], 0)

and in my, case I have all the columns in the select list with the show button checked. In your case, you have the criteria in the where clause, I don't have a where clause. If you eliminate the where clause, you will probably onliy have one value coliumn, and can put the criteria as shown above in the criteria row of that column. I did not see anything in your where clause that wouldn't be handled by the expression above.

[tennessee]

Ok, I think I figured out the problem. I went back to queries that I have used the expression that I gave you in my original response:

<>0 Or <>nz([YourColumnName], 0)

and in my, case I have all the columns in the select list with the show button checked. In your case, you have the criteria in the where clause, I don't have a where clause. If you eliminate the where clause, you will probably onliy have one value coliumn, and can put the criteria as shown above in the criteria row of that column. I did not see anything in your where clause that wouldn't be handled by the expression above.

Ok.. So I should uncheck the Show Boxes and insert the expression above? Do I have to use the same pyramid scheme as before? I dont see a where clause in the Design View of the Query..

[puppydogbuddy]

Ok.. So I should uncheck the Show Boxes and insert the expression above? Do I have to use the same pyramid scheme as before?

Just use the following sql (no where clause) as your query with show boxes checked. Criteria for each column goes in different criteria row as discussed in first pyramid, not the second pyramid.

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET];

[tennessee]

Just use the following sql (no where clause) as your query with show boxes checked. Criteria for each column goes in different criteria row as discussed in first pyramid, not the second pyramid.

SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET];

I deleted the where statements in the SQL view and then I typed in the follwing expression <>0 Or <>nz([columnname],0) in a seperate criteria row per column. This did not delete the numbers nor the column either.

Here is what my SQL view looks like:
SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>0 Or ([ESTIMATING SHEET].CPT_SY)<>nz([CPT_SY],0))) OR ((([ESTIMATING SHEET].STAIRS)<>0 Or ([ESTIMATING SHEET].STAIRS)<>nz([STAIRS],0))) OR ((([ESTIMATING SHEET].TILE_SF)<>0 Or ([ESTIMATING SHEET].TILE_SF)<>nz([TILE_SY],0))) OR ((([ESTIMATING SHEET].VINYL_SY)<>0 Or ([ESTIMATING SHEET].VINYL_SY)<>nz([VINYL_SY],0))) OR ((([ESTIMATING SHEET].WOOD_SF)<>0 Or ([ESTIMATING SHEET].WOOD_SF)<>nz([WOOD_SF],0))) OR ((([ESTIMATING SHEET].STONE_SF)<>0 Or ([ESTIMATING SHEET].STONE_SF)<>nz([STONE_SF],0)));

[puppydogbuddy]

All I can stay at this point is that it works on my end for my queries. After looking at some the things you've tried, I noticed you were using "0" rather than 0. Could it be that your columns are not of numeric data type?

In case that could be the problem we can use the Val function to convert it to numeric, like this:

<> Val(0) Or <> Val(Nz([YourColumn],0))

[tennessee]

All I can stay at this point is that it works on my end for my queries. After looking at some the things you've tried, I noticed you were using "0" rather than 0. Could it be that your columns are not of numeric data type?

In case that could be the problem we can use the Val function to convert it to numeric, like this:

<> Val(0) Or <> Val(Nz([YourColumn],0))

I checked the column properties of my Query in Design View and there was no format set for these. So I set them to General Number and used your val function expression and it still didnt work.. All the zeros are still on my report along with all of the columns..

SQL View:
SELECT [ESTIMATING SHEET].ID, [ESTIMATING SHEET].PLAN, [ESTIMATING SHEET].AREA, [ESTIMATING SHEET].CPT_SY, [ESTIMATING SHEET].STAIRS, [ESTIMATING SHEET].TILE_SF, [ESTIMATING SHEET].VINYL_SY, [ESTIMATING SHEET].WOOD_SF, [ESTIMATING SHEET].STONE_SF
FROM [ESTIMATING SHEET]
WHERE ((([ESTIMATING SHEET].CPT_SY)<>Val(0) Or ([ESTIMATING SHEET].CPT_SY)<>Val(Nz([CPT_SY],0)))) OR ((([ESTIMATING SHEET].STAIRS)<>Val(0) Or ([ESTIMATING SHEET].STAIRS)<>Val(Nz([STAIRS],0)))) OR ((([ESTIMATING SHEET].TILE_SF)<>Val(0) Or ([ESTIMATING SHEET].TILE_SF)<>Val(Nz([TILE_SF],0)))) OR ((([ESTIMATING SHEET].VINYL_SY)<>Val(0) Or ([ESTIMATING SHEET].VINYL_SY)<>Val(Nz([VINYL_SY],0)))) OR ((([ESTIMATING SHEET].WOOD_SF)<>Val(0) Or ([ESTIMATING SHEET].WOOD_SF)<>Val(Nz([WOOD_SF],0)))) OR ((([ESTIMATING SHEET].STONE_SF)<>Val(0) Or ([ESTIMATING SHEET].STONE_SF)<>Val(Nz([STONE_SF],0))));

[puppydogbuddy]

Hmm...this is a strange one... could you email a copy of your file that has been scrubbed of sensitive data? You can obtain my email address by downloading my VCard located with my profile.

[puppydogbuddy]

Hmm...this is a strange one... could you email a copy of your file that has been scrubbed of sensitive data? You can obtain my email address by downloading my VCard located with my profile.

Tim,
After looking at what you sent, I agree with you that setting the criteria row to <> 0 in the query will eliminate rows that meet the criteria, but will not eliminate columns.

To hide the columns you need to place this code in the report itself.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
 
if IsNull(YourTextBoxName1)=True or YourTextBoxName1.value = 0 then 
YourTextBoxName1.Visible=False
Your TextBoxLabel1.Visible = False 
end if 
 
if IsNull(YourTextBoxName2)=True or YourTextBoxName2.value = 0 then 
YourTextBoxName2.Visible=False 
Your TextBoxLabel2.Visible = False
end if 
 
'repeat for each textbox that you want to hide if it meets the criteria.
 
End Sub

Try it and if you have any trouble or problems, let me know.

pDog

[tennessee]

Tim,
After looking at what you sent, I agree with you that setting the criteria row to <> 0 in the query will eliminate rows that meet the criteria, but will not eliminate columns.

To hide the columns you need to place this code in the report itself.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
 
if IsNull(YourTextBoxName1)=True or YourTextBoxName1.value = 0 then 
YourTextBoxName1.Visible=False
Your TextBoxLabel1.Visible = False 
end if 
 
if IsNull(YourTextBoxName2)=True or YourTextBoxName2.value = 0 then 
YourTextBoxName2.Visible=False 
Your TextBoxLabel2.Visible = False
end if 
 
'repeat for each textbox that you want to hide if it meets the criteria.
 
End Sub

Try it and if you have any trouble or problems, let me know.

pDog

This did not work for me.. It ended up deleting columns and their headers that had information in them...

[puppydogbuddy]

This did not work for me.. It ended up deleting columns and their headers that had information in them...

Tim,
Can you post your exact code? It should have made invisible only those columns you named in your code. Thanks.

[tennessee]

Tim,
Can you post your exact code? It should have made invisible only those columns you named in your code. Thanks.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(CPT_SY) = True Or CPT_SY.Value = 0 Then
CPT_SY.Visible = False
CPT_SY_Label.Visible = False
End If
 
If IsNull(CARPET) = True Or CARPET.Value = 0 Then
CARPET.Visible = False
CARPET_Label.Visible = False
End If
 
If IsNull(STONE_SF) = True Or STONE_SF.Value = 0 Then
STONE_SF.Visible = False
STONE_SF_LABEL.Visible = False
End If
 
If IsNull(STONE) = True Or STONE.Value = 0 Then
STONE.Visible = False
STONE_LABEL.Visible = False
End If
 
End Sub

I tested it out on a 4 columns. 2 with data and 2 without. All four ended not showing up..

[puppydogbuddy]

Tim, did you modify the report from the one you sent me? When I look at your report, the textbox controls in the reporrt detail section have names like Text52, not Carpet, etc.

[tennessee]

Tim, did you modify the report from the one you sent me? When I look at your report, the textbox controls in the reporrt detail section have names like Text52, not Carpet, etc.

I might have motified the report's textobox names a couple times.. Now they are: CPT SY; CARPET; STAIRS; TILE SF; TILE; VINYL SY; VINYL; WOOD SF; WOOD; STONE SF; STONE; TOTAL

[puppydogbuddy]

Tim,
Did not forget you. While looking at your database, I realized that part of your problems are due to your design not being normalized...it is designed more like a spreadsheet than a relational db. I don't have the time to convert your design to a normalized desin, but to make things easier I will get you links to some free ms access cost estimation db templates with source code by tomorrow.

pDog