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.Vi sible="false",W OOD_SF.Visible= "true")
=IIf([WOOD_SF]="0",WOOD_SF.hi dden="true",WOO D_SF.hidden="fa lse")
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(Can cel 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.Visi ble=(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.Vi sible="false",W OOD_SF.Visible= "true")
=IIf([WOOD_SF]="0",WOOD_SF.hi dden="true",WOO D_SF.hidden="fa lse")
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(Can cel 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.Visi ble=(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.