balancing column sizes (access 2003)

[dawnd3]

Hi there, I think I asked this before but couldn't find the thread when I did a search. So forgive me if this is repetitive. I need to know how to balance columns in a sub report. If I set the sub report object to "can grow" it makes it all in one column. If I just make the object taller, it only balances out in the 3 columns if I have the sub report just the right size. I just want to automatically size the sub report so that there are 3 columns of data. Is this possible?

Thanks,

Dawn

 

[lameid]

Not readily... It would take some playing but you should be able to code setting the height based on the number of records.

In the Section's on Format event you would have to find out how many Records there are perhaps using dcount.

Next Integer Divide (backslash as opposed to forward slash) that value by 3 and add 1 to get the number of rows.

Multiply the number of Rows by the height of a row in TWIPS.

Add that to the base height of the report in Twips.

Set the height of the report to this caluclated twip value. You may also need to consider the secition height...I don't know I haven't played with this before.

FYI the report height returns in TWIPS so you should be able to find it debuging.

 

[dhookom]

The easiest method is to set the columns to display across then down. If you don't want the records displayed in this order, you can create a column in the subreport's record source that makes an "Across Then Down" look like "Down Then Across". For instance to get three columns of employees in the Northwind where the names appear alpha by lastname down then across but the layout is across then down, the Record Source query is:

SELECT DCount("*","Employees","LastName <""" & [LastName] & """") Mod 3 AS Col, Employees.*
FROM Employees;

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Forget I wrote anything... go with one of Duane's solutions much simpler.