Solved AutoHide Column in Report (1 Viewer)

[Jayessh]

I am making a Database for Payroll, I have a Report SalarySheet and I need a Column Say Bonus, but The Column Bonus will have values in only Once in a Year. i.e. it will have value in only for one Months Salary Sheet Rest 11 months it will remain empty and unnecessarily occupy space.

I need a Column which will auto hide with his Lable (Tile of the Field) when it has Zero in all the month and will show only when it has some value in any record

My Report is Tabular Format
Grouped
and based on Query

I ma using MS Access 2007

Please give me some hint or solutions

Thanks in Advance as I know all your guys are experts of Access

 

[arnelgp]

you can use the Open event of the report to "test" if the Bonus field has some value:

private sub report_open(cancel as integer)
if dcount("1", "yourTable/Query", "bonusFieldName > 0") = 0
with me.bonusTextbox
.Width = 0
.BorderStyle = 0
end with
with me.theLabelName
.Width = 0
.BorderStyle = 0
end with
end if
end sub

 

[Jayessh]

you can use the Open event of the report to "test" if the Bonus field has some value:

private sub report_open(cancel as integer)
if dcount("1", "yourTable/Query", "bonusFieldName > 0") = 0
with me.bonusTextbox
.Width = 0
.BorderStyle = 0
end with
with me.theLabelName
.Width = 0
.BorderStyle = 0
end with
end if
end sub

Thank you very much for your quick reply!

the code giving an error, It's glad if you give solutions for "Expression Builder"

 

[Gasman]

PMFJI
That is NOT what arnelgp wrote? He has no space between DCount and ( ????
Do not put spaces there.

 

[Jayessh]

PMFJI
That is NOT what arnelgp wrote? He has no space between DCount and ( ????
Do not put spaces there.

Even having same prob

 

[Gasman]

Ok, he missed that off, but was giving you the general logic?

if dcount("1", "yourTable/Query", "bonusFieldName > 0") =  0 then

 

[Jayessh]

Ok, he missed that off, but was giving you the general logic?

if dcount("1", "yourTable/Query", "bonusFieldName > 0") =  0 then

Thank you very much for correction....

Error gone but not worked... I mean the Bonus column remain Visible in Report Preview Even when it empty or filed

 

[Jayessh]

you can use the Open event of the report to "test" if the Bonus field has some value:

private sub report_open(cancel as integer)
if dcount("1", "yourTable/Query", "bonusFieldName > 0") = 0
with me.bonusTextbox
.Width = 0
.BorderStyle = 0
end with
with me.theLabelName
.Width = 0
.BorderStyle = 0
end with
end if
end sub

Not worked... I mean the Bonus column remain Visible in Report Preview Even when it empty or filed

 

[Gasman]

Does that code even run

My sub for a report open is
Private Sub Report_Open(Cancel As Integer)

which coincidentally is what arnelgp had???

 

[arnelgp]

see this sample.
the report is base on Table1.
one of the record has Bonus = 500
open the report.

close the report.
open the table and remove the 500 from that one record.
open the report again.

magic!?

 

[Jayessh]

see this sample.
the report is base on Table1.
one of the record has Bonus = 500
open the report.

close the report.
open the table and remove the 500 from that one record.
open the report again.

magic!?

Not getting Open...

 

[Gasman]

Just create the event from the Property sheet and add the code.
Also add Option Explicit to the top of module.

arnelgp uses a much later version of Access which 2007 will not open. I know as that is what I use.

 

[arnelgp]

you have old version of access.
the attached is created on a2003.

 

[Jayessh]

you have old version of access.
the attached is created on a2003.

I have MS Access 2007

 

[bob fitz]

Cross-Posted: https://www.accessforums.net/showthread.php?t=82125

Please read: https://www.excelguru.ca/content.php?184

 

[Gasman]

Cross-Posted: https://www.accessforums.net/showthread.php?t=82125

Please read: https://www.excelguru.ca/content.php?184

Well I hope they have more luck.

I'm out of this thread, because of that.

 

[arnelgp]

this is made in A2007.

 

[Jayessh]

this is made in A2007.

Thank You Thank You Thank You Thank You Thank You Very Much !

I got what should I do... and yes your Database is opening after re-downloading....

I am trying this trick in my db... Hope you don't mind if I asked you further queries if any


Thanks Once Again !

 

[arnelgp]

no problem, just shoot your questions.

 

[Jayessh]

no problem, just shoot your questions.

Now it is looking like this.... (ScreenShot Attached)
My Bonus Column is before the Net Salary so if the Column disappears Net Salary should shit to its place


Is it Possible ???