Conditional sub grouping 1

[1DMF]

Hi,

I have two reports, one shows Life Insurance the other General Insurance which i want to merge to one report, they are both identical except for one of the reports the boss didn't want sub grouping by 'Insurer' on the other he did.

The reports are small enough to merge together and I could use use the 'Insurance Type' as the master grouping to split out Life from GI however, I would want to be able to wrap a condition around one of the sub groups to say only show sub group details if Life Insurance.

is it possible to wrap a condition around a grouping within MS Access Reports?

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

If they are both identical, I would probably create a single report based on a union query. You can derive a new column in the union query based on the "Life" or "General". It would help to get more specifics if you want a more specific solution.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Not sure what you're suggesting, the report data is collated via a VBA sub routine and the reporting data is inserted into the report table.

I have to generate the data this way as the actual records have no category 'Life' / 'GI' , the product types get far more complicated than that, so I grab the records into a record set and loop.

I match product types for the specific category and insert into the reporting table, at this point I could add an additional field which would identify GI vs Life, so not sure what this union query is you suggest.

attached is a screen shot of the Life report, if I added the two record types into the report table, I could group by a GI / Life header..but the boss doesn't want the 'Insurer Header' sub group when it is GI.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

If you have two reports, I expect there are two record sources. Since the reports are nearly identical, I would also expect the fields/columns to be close enough that you could create a union query of the two record source queries to base a report on.

Are my assumptions correct?
Do you know how to create a union query?
Do you understand that you could create a calculated/derived column in the union query?

You can add code in a group header section On Format event to cancel its display if a control in the header equals a particular value.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

You can add code in a group header section On Format event to cancel its display if a control in the header equals a particular value.

that's the nugget I was after, many thanks!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

can someone please help with syntax, I seem to be getting an error with the following..

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

If (Me.Ins_Type = "GI") Then
    Cancel = True
End If


End Sub

I'm assuming you make cancel = true to stop the header being printed, but it keeps erroring saying that the field doesn't exist, but it does?

if I type me. , the list appears and I select it, yet when the report runs it says it doesn't exits.

What am I doing wrong?

Thanks,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

VBA in reports is a little persnikkety (technical jargon). You must bind Ins_Type to a control in the report section. If it isn't bound to a control, your code will result in the error you posted.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Thanks, works a treat now.

I was using an IIF statement rather than just displaying the field data.

=IIf([Ins_Type]='Life',"Life Insurance","General Insurance")

would have thought you couldn't do that either if the field wasn't bound.

like you said, it's all a bit pernickety!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!