Report format problem

[CHTHOMAS]

I have a report in the following format.

Discipline Header
Category
Discipline footer TOTAL

eg.

A11 CIVIL AND STRUCTURAL
......................... ENGINEERING 10
......................... .....DRAFTING 10
A11 CIVIL AND STRUCTURAL TOTAL 20


A11 CIVIL AND STRUCTURAL is the Discipline Header
Engineering, Drafting are the Categories
A11 CIVIL AND STRUCTURAL TOTAL is the Discipline footer which sums the 2 fields.

The report works fine. Now my client wants to reduce the report size in such a way that

If there is value in only 1 category, say ENGINEERING with a value 10 and Drafting is 0, then it should appear in a single line as below

A11 CIVIL AND STRUCTURAL ENGINEERING 10

ie The header and footer shouldn't be shown. Instead the Discipline followed by Category should appear.

Is it possible to do that? I tried hiding and showing the Category part counting the number of records. But not quite working out. Any ideas or solution highly appreciable.

Regards,

Charley

 

[BattleDroid42]

Not sure what your data looks like, but I would suggest changing the WHERE clause in the SQL statement behind the report so these records don't even appear in the report:

WHERE = FieldName <> 0

 

[CosmoKramer]

One way to do this is to duplicate what the &quot;Block layout&quot; does in the report wizard.

Move the Discipline text box from its' header to the detail section, and set the Visible property of the header to No. Set the Hide Duplicates property of the Discipline control to Yes.

Now, to only display the group footer when there are two or more detail records:

Add an unbound, invisible text box (txtCounter) to the detail section. Set its Control Source to =1, and its Running Sum property to Over Group.

In the On Format event of the group footer, place code like this to set the section to be invisible if there is only one detail record:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
If [txtCounter] = 1 Then
   Me.GroupFooter1.Visible = False
Else
   Me.GroupFooter1.Visible = True
End If
End Sub

You may have to play with the formatting after this to get it to look &quot;right&quot;, but it does work.....

Let me know if this helps.....