Finding last line-item in Group

[Meltdown]

Hi all,

I have a report and there is a grouping on the report called 'Options'.

The report prints something like this:

Option 1
- Line Item 1
- Line Item 2
- Line Item 3 -(Show number here)

Option 2
- Line Item 1
- Line Item 2
- Line Item 3
- Line Item 4
- Line Item 5 - (Show number here)

Option 3
- Line Item 1
- Line Item 2 - -(Show number here)

.... and so on

Each report is different, so the number of line items is unknown, not fixed as in my example above.

What I need is to find the last item in each group and display a number beside
the last line-item. I will set the number to invisible for all the other line-items.

Does anyone know how I might be able to find the last line-item in each group?

Thanks Melt

 

[vbaInet]

Get the number of lines by counting the records per group using a DCount() function or a subquery (if you're familiar with that). From that count you can then either via the control source set the value using an IIF() function by comparing the Group Count to the count returned by the field.

So in your query (which should be the record source of your report) the DCount() could look like this:

AliasName: DCount("*", "Table or Query Name", "[GroupID] = " & [GroupID])

If GroupID is text then this:

AliasName: DCount("*", "Table or Query Name", "[GroupID] = '" & [GroupID] & "'")

In your report you need a textbox that will be a running count over group. You can find the Running Sum property of the textbox under the DATA tab of its property sheet.

The IIF() function for the textbox that will display the value will look like this:

=IIF(txtRunningSum = [AliasName], [DisplayNumber], Null)

No need setting the visible property.

 

[Meltdown]

Thanks vbaInet, great explanation, got it sorted based on your instructions, much appreciated.

Regards
Melt

 

[vbaInet]

Glad we could help.