By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Hide no data fields in access reports

P: 56
Hi,
I am new to ms-access, i need some help on reports. I prepared payroll transactions for staff in which there are 14 fields. I want to hide some fields which has no data (only zeros). I want to write a code to whole report not for a particular field. If should appear when some data is there not zeros.

can somebody help me out in this plzzzzzzzzzz

vsts
Sep 18 '07 #1
Share this Question
Share on Google+
24 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,
I am new to ms-access, i need some help on reports. I prepared payroll transactions for staff in which there are 14 fields. I want to hide some fields which has no data (only zeros). I want to write a code to whole report not for a particular field. If should appear when some data is there not zeros.

can somebody help me out in this plzzzzzzzzzz

vsts
You will have to do each field individually and put the code in the format event of the report. Something like

Expand|Select|Wrap|Line Numbers
  1. If Me!TxtBoxName = 0 Then
  2.     Me!TxtBoxName.Visible = False
  3. Else
  4.     Me!TxtBoxName.Visible = True
  5. End If
  6.  
Sep 18 '07 #2

P: 56
Thank you for your support... actually i have a field "dearnesspay arrears" in my database. when i enter this field name in the code given by you it is showing error on "arrears". is there any problem in field's name..
please guide me
and the place should be covered by the next field...

vsts
Sep 19 '07 #3

P: 56
Thank you .. its working and the space is not covering by the next field. I need one more help calculation of PF is applicable for only some of the employees but as per my query its calculating for all the employees.. can you help me in this too..
Sep 19 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you .. its working and the space is not covering by the next field. I need one more help calculation of PF is applicable for only some of the employees but as per my query its calculating for all the employees.. can you help me in this too..
Post theSQL of the query you are currently using. Indicate which of the fields would be used to decide which employees are applicable and how.
Sep 19 '07 #5

P: 56
Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.EmployeesID, Employees.[Salary for the month of], Employees.EmployeeNumber, Employees.FirstName, Employees.[Bank Number], Employees.Basic, Employees.[Basic Arrears], [Basic]*0.5 AS Expr1, Employees.[Dearnesspay Arrears], ([Basic]+[Expr1])*0.41 AS Expr2, Employees.[DA Arrears], ([Basic]+[Expr1])*0.3 AS Expr3, Employees.[HRA Arrears], Employees.CCA, Employees.[CCA Arrears], Employees.[Conveyance Allowance], Employees.[Cony Arrears], [Basic]+[Basic Arrears]+[Expr1]+[Dearnesspay Arrears]+[Expr2]+[DA Arrears]+[Expr3]+[HRA Arrears]+[CCA]+[CCA Arrears]+[Conveyance Allowance]+[Cony Arrears] AS Expr4, ([Basic]+[Basic Arrears]+[Expr1]+[Dearnesspay Arrears]+[Expr2]+[DA Arrears])*0.12 AS Expr5, Employees.VPF, Employees.PT, Employees.TDS, Employees.[BENV FUND], Employees.[WEL FUND], Employees.[CCC HYD], Employees.[Vehicle Loan], Employees.[PC Advance], Employees.[GPF Advance], Employees.HBA, Employees.CGEIS, Employees.[Lease Recovery], [Expr5]+[VPF]+[PT]+[TDS]+[BENV FUND]+[WEL FUND]+[CCC HYD]+[Vehicle Loan]+[PC Advance]+[GPF Advance]+[HBA]+[CGEIS]+[Lease Recovery] AS Expr6, [Expr4]-[Expr6] AS Expr7
  2. FROM Employees;
This is the SQL view of the query.. fields are Employees.Basic to [HRA Arrears]
and PF applicable for only first 19 members...

I asked for covering the vacant place by next field...


Thank you for your support
Sep 19 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Set the can shrink property of all the textboxes to yes.

Now what do you mean by the first 19 members?
Sep 19 '07 #7

P: 56
i put can shrink property to "yes" but still it showing the space

from 1 - 19 records only the pf is applicable
Sep 19 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
i put can shrink property to "yes" but still it showing the space

from 1 - 19 records only the pf is applicable
Yes but how do I know if pf is applicable and when you say from 1-19 are you talking about the EmployeesID?
Sep 19 '07 #9

P: 56
from 1st record to 19th record...employee ids are there but numbers are scattered there is no formality in numbering bcos we have some other branches also.

what about the space in the reports?
Sep 19 '07 #10

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

You may take a look at the similar thread
"Moving" a line in a Report based on 2 conditions
Sep 19 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
from 1st record to 19th record...employee ids are there but numbers are scattered there is no formality in numbering bcos we have some other branches also.

what about the space in the reports?
You can use SELECT TOP 19 ....

However, there is no guarantee which records will be returned as you haven't specified a sort order. Also you still haven't told me how to distinguish which employees have pf applicable.
Sep 19 '07 #12

P: 56
Hi,
thanks for your support... i went through the page suggested by you.

i have fields like pf, vpf, pt, tds, bnvfund, extra.. in some records there are no values for pf, vpf, pt, tds and have values in bnvfnd. so i want to hide all the first four fields. can you suggest me the code for this.

Thank you,
vsts
Sep 20 '07 #13

P: 56
Hi,
i am able to hide the null values in the report but the space is not covered by the next field... can you suggest me in this to cover the space.. i made all the fields "CAN SHRINK"..but nothing is working.

thank you for your support
Sep 20 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,
i am able to hide the null values in the report but the space is not covered by the next field... can you suggest me in this to cover the space.. i made all the fields "CAN SHRINK"..but nothing is working.

thank you for your support
Did you read the thread FishVal directed you to?
Sep 20 '07 #15

P: 56
ya i read that but i am unable to correalate it to my dastabase
Sep 20 '07 #16

FishVal
Expert 2.5K+
P: 2,653
ya i read that but i am unable to correalate it to my dastabase
Hi, there.

Can you clarify the logic of the report layout compacting?
I mean the following.
If all your fields are arranged in one column it is quite understandable that you want to lift field with value to cover the place of field that has no value and then shrink an empty space at the bottom.
If you have fields arranged in several columns or moreover arranged in some groups how the are supposed to be compacted?
And the last. Do you have additional controls, e.g. frames, lines, drawings needed to be changed to complete layout compacting?

P.S. Plz, provide screenshot.
Sep 20 '07 #17

P: 56
I have fields in two columns... no line or other structures are there,
no need to shift all the rows but i need it for only some rows..
for ex: I want eliminate fields like pf, tds, vpf etc because it is applicable for only some employees and it should not appear for others thats the main problem

please guide me

thank you
Sep 21 '07 #18

P: 56
This is the format i designed in report:

I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks

like this upto 10 rows.
after that total of above coloums

total of above coloums
after that total of above coloums
Sep 21 '07 #19

P: 56
This is the format i designed in report:

I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks

like this upto 10 rows.
after that total of above coloums
tax applicable grand total
Sep 21 '07 #20

FishVal
Expert 2.5K+
P: 2,653
This is the format i designed in report:

I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks
I1 a1 r1 amount1 remarks

like this upto 10 rows.
after that total of above coloums

total of above coloums
after that total of above coloums
Sorry, this doesn't make a sense.

What is
I1
a1
r1
amount1
remarks
Sep 21 '07 #21

P: 56
Item1 Quantity1 Rate1 Amount1 remarks
Item2 Quantity2 Rate2 Amount2 remarks
Item3 Quantity3 Rate3 Amount3 remarks
Item4 Quantity4 Rate4 Amount4 remarks
Total of Amount coloum
Tax applicable on total amount
grand total

i want to replace the third and fourth rows with total amount, tax applicable and grand total rows if there is no data in third and fourth rows.
Sep 21 '07 #22

FishVal
Expert 2.5K+
P: 2,653
Item1 Quantity1 Rate1 Amount1 remarks
Item2 Quantity2 Rate2 Amount2 remarks
Item3 Quantity3 Rate3 Amount3 remarks
Item4 Quantity4 Rate4 Amount4 remarks
Total of Amount coloum
Tax applicable on total amount
grand total

i want to replace the third and fourth rows with total amount, tax applicable and grand total rows if there is no data in third and fourth rows.
Aha.

Very clear explanation.

What each word does mean: Label, Bound TextBox, Unbound TextBox?
If some are Labels and some are Textboxes, then which are related one to another and need to be moved together?
If table-bound controls are arranged in 2 columns (as you've said), then would you like to shift controls in the columns separately or allow controls from column 2 to be moved to column 1 (very different programming logic)?
You provide RowSource with 20+ fields and couple of identical rows with 5 words as layout.

Provide
  • screenshot of report design view
  • screenshot(s) of report with empty rows and explanation how the should be compacted

Kind regards,
Fish
Sep 21 '07 #23

P: 56
Sir/Madam,

I didnt have that much knowledge about access... I started using it one month back only and trying to learn it on my own... what i did upto this stage is purely my own knowledge... i am searching for online help and i am able trace one database in which i saw they wrote code for hiding one ole object. I tried it for my database and later i found this site with much needed help... if u ask me what is this what is that i am unable to explain sorry if i wasted ur time

thank you for your help
Sep 21 '07 #24

FishVal
Expert 2.5K+
P: 2,653
Sir/Madam,

I didnt have that much knowledge about access... I started using it one month back only and trying to learn it on my own... what i did upto this stage is purely my own knowledge... i am searching for online help and i am able trace one database in which i saw they wrote code for hiding one ole object. I tried it for my database and later i found this site with much needed help... if u ask me what is this what is that i am unable to explain sorry if i wasted ur time

thank you for your help
Not a problem.

I'll try to explain the logic of possible solution so you'll understand what kind of information I need too come with real advices.
  • code implementing the solution logic is in fromat event handler, it fires every time Access formats detail section of the report
  • code detects empty field, hides it and shift the next field to this one
    • by "field" I mean a couple of controls, e.g. you have [EmployeeNumber] in the RecordSource, I guess in report layout you have Label control (Something like "Employee #:") and TextBox control (e.g. to the right of the Label control)
    • obviously you need to move both'em, so the code needs to determine these couples
    • these "fields" need to be ordered in such a way that code will recognize what "field" is previous and what is next in moving sequence
    • "fields" may be arranged in several columns, so it will be several sets of ordered "fields"

It will be very helpful if you will provide screenshots.

P.S. Sir/Madam is too oficial. Dear friend will be more acceptable. :)
Sep 21 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.