How can i seperate my detail section?

[SirStevie3]

so i have a report based on a union query. i set up the query so that it can be grouped by month on the report. (see pics)

The problem is that each detail section in the design view just shows one row of text boxes, a "Num" and a "Label". i'd like to be able to seperate this in design view so that i can format different numbers different ways and group relevent items together, all while staying within that "MonthGroup".

The way the report looks now is ok... but for instance, i want to format the "Reduced ERC workload by (%):" to be an actual percentage.

my logic tells me that i'll need to create text boxes that will have some sort of function that matches the "Monthgroup" in the detail section to the MonthGroup header control. then in addition to that, each text box will have to display only one number for that month (ie. "Number of ERC Submittals" or "Number of documents requested from provisioners") so i can adjust the layout and format of each.

can someone help me get started by seperating the items in the detail section so they are all viewable in design view?

Ps. i have a bad feeling someone is going to tell me that i need to redesign my union query/queries...

 

[SirStevie3]

forgot to attach pics.

 

[CJ_London]

I think you have your excel head on

Consider using a subquery to calculate your percentages

Without knowing how your union query is constructed, it is not possible to advise further.

 

[CJ_London]

Sorry - misunderstood your post

What I have done in the past is to bring through all the values as text, rather than numberic and have then formatted as required within each part of the query

 

[SirStevie3]

thanks for the input.

as far as i can tell (from experimentation) the formats of a query do not carry through to the union query, nor to the report.

not sure how that would help me individualize the items of the detail section...

 

[CJ_London]

you need to use the format function e.g.

format(myPercentage,"0.00%") as txtPercentage
format(myLong,"0") as txtLong
format(myDecimal,"0.00") as txtDecimal

 

[SirStevie3]

ok, after reading your advice and thinking i went with formatting the union query via SQL statements. worked well!!

now my numbers look better, thank you for that.

but still, i would like to move some things in the detail section around a bit to make the report more visually appealing. is this possible?

 

[CJ_London]

Depends what you mean by moving around and how you determine where to move it.

For example, if you want the percentages to be in a separate column you can do one of two things.

1. Modify your union query to bring through two columns, and populate the column you don't want with a zero length string ("") - note do not use null.

2. in the onformat event, put some vba code which changes the control left property. Perhaps something like this pseudo code (not tested)

Select Case label.caption
Case "Reduced ERC word load by (%):")
    num.left=num.left+1440 ' moves control 1" to right
end select