Sorting records in Detail section into different columns

D

Darleen

I am having some report formatting trouble.

My fields are as such:
HouseID Option Name Option Type
34 Sunroom Structural
34 Loft Structural
34 Side Entry Garage
34 Service Door Garage
34 Dining Bay Bay Windows

In my report, I want to list these options each in a different column
according to type. So one column for Structural, one for garage,
etc... I can do this with different textbox controls that each have
an if statement as their source: iif([Option
type]="Structural",[option type],null).

This works, but there are horizontal gaps between records in each
column because each option type actually is a new record and so it
goes on a new line.

My data ends up looking like this:

Structural Bay Windows Garage
*********************************************************
Sunroom
Side Entry
Loft
Dining Bay
Service Door


How can I "zip up" these columns? Should I use unbound listboxes? I
tried that and it didn't work for a couple reasons: listboxes do not
have a CanGrow or CanShrink property, and also they just wouldn't show
anything. The recordsource for them was
"SELECT OptNames.Option, OptNames.Type, OptCommunityPlans.fkPlanID
FROM OptNames
RIGHT JOIN OptCommunityPlans ON OptNames.ID=OptCommunityPlans.OptID
WHERE (((OptNames.Type)=2) AND ((OptCommunityPlans.fkPlanID) Like
[id]));"

Does anyone know how I can accomplish this?

Thank you in advance.
 
C

Chris Large

Hi

You'll need subReports to do this.

Create a report that has your HouseID, optName and optType
fields in its source, but add only the optName to the
detail section (hide all the other sections).

Next create another form which has a source of just the
unique HouseIDs. Add the HouseID to the detail section and
then add text boxes (1 for each of the optTypes) set the
Name to be say txtStructural and the control source to be
="Structural" repeat for each of the optTypes.

Next add add the first report you created as a subReport
set the LinkChild Fields property to be
[HouseID];[optType]
and the LinkMaster Fields to be
[HouseID];[txtStructural]
add the same subreport again for each optType, just
changing the LinkMaster fields property to point at the
correct text box.

hth

Chris

-----Original Message-----
I am having some report formatting trouble.

My fields are as such:
HouseID Option Name Option Type
34 Sunroom Structural
34 Loft Structural
34 Side Entry Garage
34 Service Door Garage
34 Dining Bay Bay Windows

In my report, I want to list these options each in a different column
according to type. So one column for Structural, one for garage,
etc... I can do this with different textbox controls that each have
an if statement as their source: iif([Option
type]="Structural",[option type],null).

This works, but there are horizontal gaps between records in each
column because each option type actually is a new record and so it
goes on a new line.

My data ends up looking like this:

Structural Bay Windows Garage
*********************************************************
Sunroom
Side Entry
Loft
Dining Bay
Service Door


How can I "zip up" these columns? Should I use unbound listboxes? I
tried that and it didn't work for a couple reasons: listboxes do not
have a CanGrow or CanShrink property, and also they just wouldn't show
anything. The recordsource for them was
"SELECT OptNames.Option, OptNames.Type, OptCommunityPlans.fkPlanID
FROM OptNames
RIGHT JOIN OptCommunityPlans ON OptNames.ID=OptCommunityPlans.OptID
WHERE (((OptNames.Type)=2) AND
Click to expand...
((OptCommunityPlans.fkPlanID) Like
[id]));"

Does anyone know how I can accomplish this?

Thank you in advance.
.
Click to expand...
 
Top