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

[talktozee2]

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.

[Delerna]

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

[Delerna]

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

[talktozee2]

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...

[Delerna]

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?

[talktozee2]

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.

[Delerna]

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.

[talktozee2]

Almost like what this person is trying to do:

** Edit ** Link to competing forum removed as per site rules.

[talktozee2]

date    typeID    amount    investorID
6/1/2006    2    $600     11
6/1/2006    2    $100     5
6/1/2006    2    $440     10
6/1/2006    2    $160     7
6/1/2006    2    $2,000     3
6/1/2006    2    $440     2
6/1/2006    2    $260     1
6/2/2006    4    $39     0
6/3/2006    2    $100     13
6/3/2006    2    $478     16
6/3/2006    2    $428     12
7/31/2006    4    $53     0
8/31/2006    4    $57     0
9/30/2006    4    $54     0
10/31/2006    4    $54     0
11/30/2006    4    $57     0
12/31/2006    4    $61     0
1/31/2007    4    $69     0
2/28/2007    4    $71     0
3/31/2007    4    $77     0
4/30/2007    4    $65     0
5/31/2007    4    $59     0
6/30/2007    4    $62     0
7/31/2007    4    $70     0
8/31/2007    4    $69     0
9/30/2007    4    $62     0
10/6/2007    2    $303     1
10/6/2007    2    $1,518     3
10/6/2007    1    $20     0
10/6/2007    2    $300     14
10/6/2007    2    $308     6
10/6/2007    3    ($569)    16
10/6/2007    2    $1,047     11
10/6/2007    2    $209     5
10/6/2007    2    $2,374     10
10/31/2007    4    $122     0
11/30/2007    4    $127     0
12/1/2007    2    $353     14
12/1/2007    2    $150     15
12/1/2007    2    $284     21
12/31/2007    4    $136     0
 

1. sum the amount column as you go (current total)

2. do some math

If the typeID <=3 then
  if the investorID = the investor ID on an opened form, then
    the investor's total = the investor's total + the current amount
  end if
 
  percentage = investor's total / current total
 
else if the typeID > 4 then
 
  investor's total = percentage * current total
 
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:

1/31/2007    283.72
2/28/2007    287.46
3/31/2007    291.47
4/30/2007    294.86
5/31/2007    297.95
6/30/2007    301.21
7/31/2007    304.88
8/31/2007    308.49
9/30/2007    311.71
10/31/2007    620.98
11/30/2007    627.82
12/31/2007    634.09

[Delerna]

I'll check it out when I get home tonight

[talktozee2]

Thanks, I appreciate it. The math may be a bit off, but that's because it's dummy data. But it should be close.

[Delerna]

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

[Delerna]

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.

SELECT YourTable.ID, 
     YourTable.date,
     YourTable.typeID, 
     YourTable.amount, 
     YourTable.investorID, 
     (SELECT sum(a.[amount]) FROM YourTable a  where a.ID<=YourTable.ID) AS RunTot
FROM YourTable;
 

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.

SELECT YourTable.ID, 
     YourTable.date, 
     CDate(Month(DateAdd("m",1,[date])) & "/01/" & Year(DateAdd("m",1,[date])))-1 AS dte, 
     YourTable.typeID, 
     YourTable.amount, 
     YourTable.investorID, 
     (   SELECT sum(a.[amount]) 
         FROM YourTable a  
         WHERE a.ID<=YourTable.ID 
               AND year(YourTable.date)=year(a.date) 
               AND month(YourTable.date)=month(a.date)
     ) AS MthRunTot
FROM YourTable;
 

Item 2 in your example can be accomplished with an

FieldName: iif(TypeID<=3,iif(condition,true,false),iif(TypeID>4,true,false)) 
 

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.

[talktozee2]

- 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

[talktozee2]

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... ;)