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

Hide rows in report & eliminate whitespace, but still reference the values

P: 14
I'm basically trying to replicate Excel in an Access report.

I have 30+ rows of data from my query, and I need all 30+ rows to perform some calculations in VBA to distill the 30+ rows down to 12.

I'm able to get the data and perform the calculation AND hide the unwanted rows by setting their Height = 0, but they still take up whitespace.

I've tried to set the Visible property on each row to False, but then I can't reference them in VBA for some reason (by design?) and none of my IF-THEN statements work.


Basically, all I want are my 12 rows of data in a nice stack with no whitespace.
Feb 25 '08 #1
Share this Question
Share on Google+
14 Replies


Delerna
Expert 100+
P: 1,134
Its been a while since I done any real development work in access.
What happens if you set the width property to 0. I would think that that will achieve what you require
Feb 25 '08 #2

Delerna
Expert 100+
P: 1,134
Just re-read your question and I see that you are actually trying to hide entire rows not just certain fields in the row. Hmmmm
Feb 25 '08 #3

P: 14
Right. And I can "hide" the rows on the report (i.e. they are "white") but they still take up space. I've tried the Move method, the Visible, Left, Top, Width and Height properties but nothing is working. It's been driving me nuts...
Feb 25 '08 #4

Delerna
Expert 100+
P: 1,134
I don't see fully why there are 30 + rows that you are trying to reduce down to 12 via calculations in VBA.

Why cant you do that in the query the report is based on. Or even the grouping functionality of the report?
or maybe even a combination of the two?
Feb 25 '08 #5

P: 14
Because each new row's data is based upon running totals and percentages that are calculated as each previous row of data is processed. Do you think this type of math is possible in a query? If so, I'm all ears.
Feb 25 '08 #6

Delerna
Expert 100+
P: 1,134
Yes I think so, post a descption of what your report is doing and some dummy data that illustrates what you want to do.
For exaple, Here is some data and here is what I want it to looklike on the report, and I will see what I can come up with. Or else someone else may come up with an answer. I have devinitely achieved running totals in queries before.
Feb 25 '08 #7

P: 14
Almost like what this person is trying to do:

** Edit ** Link to competing forum removed as per site rules.
Feb 25 '08 #8

P: 14
Expand|Select|Wrap|Line Numbers
  1. date    typeID    amount    investorID
  2. 6/1/2006    2    $600     11
  3. 6/1/2006    2    $100     5
  4. 6/1/2006    2    $440     10
  5. 6/1/2006    2    $160     7
  6. 6/1/2006    2    $2,000     3
  7. 6/1/2006    2    $440     2
  8. 6/1/2006    2    $260     1
  9. 6/2/2006    4    $39     0
  10. 6/3/2006    2    $100     13
  11. 6/3/2006    2    $478     16
  12. 6/3/2006    2    $428     12
  13. 7/31/2006    4    $53     0
  14. 8/31/2006    4    $57     0
  15. 9/30/2006    4    $54     0
  16. 10/31/2006    4    $54     0
  17. 11/30/2006    4    $57     0
  18. 12/31/2006    4    $61     0
  19. 1/31/2007    4    $69     0
  20. 2/28/2007    4    $71     0
  21. 3/31/2007    4    $77     0
  22. 4/30/2007    4    $65     0
  23. 5/31/2007    4    $59     0
  24. 6/30/2007    4    $62     0
  25. 7/31/2007    4    $70     0
  26. 8/31/2007    4    $69     0
  27. 9/30/2007    4    $62     0
  28. 10/6/2007    2    $303     1
  29. 10/6/2007    2    $1,518     3
  30. 10/6/2007    1    $20     0
  31. 10/6/2007    2    $300     14
  32. 10/6/2007    2    $308     6
  33. 10/6/2007    3    ($569)    16
  34. 10/6/2007    2    $1,047     11
  35. 10/6/2007    2    $209     5
  36. 10/6/2007    2    $2,374     10
  37. 10/31/2007    4    $122     0
  38. 11/30/2007    4    $127     0
  39. 12/1/2007    2    $353     14
  40. 12/1/2007    2    $150     15
  41. 12/1/2007    2    $284     21
  42. 12/31/2007    4    $136     0
  43.  
1. sum the amount column as you go (current total)

2. do some math

Expand|Select|Wrap|Line Numbers
  1. If the typeID <=3 then
  2.   if the investorID = the investor ID on an opened form, then
  3.     the investor's total = the investor's total + the current amount
  4.   end if
  5.  
  6.   percentage = investor's total / current total
  7.  
  8. else if the typeID > 4 then
  9.  
  10.   investor's total = percentage * current total
  11.  
  12. end if
3. Now if the date is <= a date on the same opened form -OR- the typeID < 4, don't show the row


The resulting data would look like this:

Expand|Select|Wrap|Line Numbers
  1. 1/31/2007    283.72
  2. 2/28/2007    287.46
  3. 3/31/2007    291.47
  4. 4/30/2007    294.86
  5. 5/31/2007    297.95
  6. 6/30/2007    301.21
  7. 7/31/2007    304.88
  8. 8/31/2007    308.49
  9. 9/30/2007    311.71
  10. 10/31/2007    620.98
  11. 11/30/2007    627.82
  12. 12/31/2007    634.09
Feb 25 '08 #9

Delerna
Expert 100+
P: 1,134
I'll check it out when I get home tonight
Feb 25 '08 #10

P: 14
Thanks, I appreciate it. The math may be a bit off, but that's because it's dummy data. But it should be close.
Feb 25 '08 #11

Delerna
Expert 100+
P: 1,134
I guess you are calling the report from a form so
another thing you could try is a table to store the results into
(the 12 or so records)
in the button click event (if indeed you are using a button to open the report)
open a recordset object with the 30+ records and perform all the calculations that you are currently doing and save the resulting 12 records into the table.

Then all you need do is change the report to work off the results table, no more 30+ records to worry about
Feb 25 '08 #12

Delerna
Expert 100+
P: 1,134
Ok, I'm still not getting a clear picture of your situation and since you must have some level of experience above newbie I will desribe in general terms how to generate a running total in a query.
Basically, you need some way of sequencing the records. An auto number field is the easiest, but you can also use a date and time sequence, so long as any particular date and time will references a unique record. Also when doing this you don't want to have too many records to work with as it can become quite slow. This is because you are qoing to be executing a summing query against each record. Here is a query I have generated from the data you supplied that gets the running total.

Expand|Select|Wrap|Line Numbers
  1. SELECT YourTable.ID, 
  2.      YourTable.date,
  3.      YourTable.typeID, 
  4.      YourTable.amount, 
  5.      YourTable.investorID, 
  6.      (SELECT sum(a.[amount]) FROM YourTable a  where a.ID<=YourTable.ID) AS RunTot
  7. FROM YourTable;
  8.  
I am using an auto number field, ID, to sequence the rows.
Having done that I noticed that you seem to be getting a running total by month starting the count over again at the start of each month. Not sure about that though. Anyway here is my attempt at a monthly running total.
Expand|Select|Wrap|Line Numbers
  1. SELECT YourTable.ID, 
  2.      YourTable.date, 
  3.      CDate(Month(DateAdd("m",1,[date])) & "/01/" & Year(DateAdd("m",1,[date])))-1 AS dte, 
  4.      YourTable.typeID, 
  5.      YourTable.amount, 
  6.      YourTable.investorID, 
  7.      (   SELECT sum(a.[amount]) 
  8.          FROM YourTable a  
  9.          WHERE a.ID<=YourTable.ID 
  10.                AND year(YourTable.date)=year(a.date) 
  11.                AND month(YourTable.date)=month(a.date)
  12.      ) AS MthRunTot
  13. FROM YourTable;
  14.  
Item 2 in your example can be accomplished with an
Expand|Select|Wrap|Line Numbers
  1. FieldName: iif(TypeID<=3,iif(condition,true,false),iif(TypeID>4,true,false)) 
  2.  
in the queries select. Where condition references the control on the form that you mention.

Item 3 will just be criteria in the query and the date criteria will reference the date control on the form that you mention.

Anyway, what I have put here won't fit what you need exactly but I hope I have given you enough for you to tailor these ideas to achieve your goal.
Post back if you need further help or even if you come up with a solution different to this. We can all learn from each other here.
Feb 26 '08 #13

P: 14
- Yes, I'm calling the report from a from using a button
- I like the idea of storing the results in a table and then performing calculations to get the 12 rows, but this all seems a bit excessive to remove whitespace from a report

Regarding your second solution:
- The table does have an autonumber field, so that's not a problem
- I do run a calculation on each "month" (actually, each transaction), but only to determine whether the investor's total or percentage has changed.

I have experimented with IIF's, but again, to remove some whitespace I can't believe it's this difficult. lol
Feb 26 '08 #14

P: 14
Wow, a major breakthrough. I was writing all of this code in a separate Module in Access; instead, I moved all of the code into the actual report and voila, everything started to work. I was under the impression that it had to be in a Module for more global accessibility but putting the code for hiding rows (using the Visible property) and for running the calculations was MUCH easier once it was in the actual report's VBA code section.

Thanks for your help!

To summarize:

1. During the report's read of each row I made each textbox invisible (Visible = False)
2. If the date or typeID wasn't correct I left the row's controls alone (still invisible).
3. Else, I made certain columns (date and investment) Visible.
4. I then ran the calculations and was even able to completely delete 4 textboxes from the report and the extra module I was using by putting the variables I needed in the report's code section.

Now, if I can only get this darn thing to run as a sub-report... ;)
Feb 26 '08 #15

Post your reply

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