grouping on unbound detail field

[Andy]

I have a report based on a simple query with the sourced fields displayed in
the detail line - fine.

I also have an unbound field in the detail line which has its value
calculated in the On Format event procedure. I use 2 of the sourced fields
plus a value read in fom an external Excel Workbook to calculate the unbound
value.

I need to group the report based on the unbound field but in the Sorting and
Grouping wizard the unbound field cannot be selected (only the sourced fields
are visible).

Any ideas how I might achieve this? - I can't build this calculated unbound
field into the original saved query due to the dynamic value being read in
from Excel as the report is displayed.

A good one for discussion.

Thanks.

 

[Allen Browne]

Andy, this is not going to work.

Reports work on a forward scrolling recordset (snapshot). They have no idea
what is yet to come. If the value is only generated at the time each record
is formatted, that is too late to sort all the records in the report by that
value, even the ones that have already been formatted.

Additionally the Format event for the section only occurs at the time Access
tries to fit this record onto the page. That means that if you preview the
report and just print page 4, the Format event for the records on pages 2
and 3 probably won't even fire.

There is therefore no way to sort on a result that is calculated in the
Format event of one of the sections.

As an alternative, it might be possible to move whatever code you have in
this event out into a function in a general module, and then create a query
based on the linked Excel table, with the function as a calculated field in
the query. Then use this query as the source for the report. It would then
be possible to sort on the calculated field.

 

[Larry Linson]

It may not be possible, but consider if you can link the Excel Workbook, and
include that in the Query so you can calculate the value in the Query.

You can't do sorting and grouping on an unbound field, because the sorting
and grouping is done before the field will be set. Thus, if you are going to
be able to use it for grouping, it has to be part of the RecordSource (your
Query).

Larry Linson
Microsoft Access MVP

 

[Marshall Barton]

I have a report based on a simple query with the sourced fields displayed in
the detail line - fine.

I also have an unbound field in the detail line which has its value
calculated in the On Format event procedure. I use 2 of the sourced fields
plus a value read in fom an external Excel Workbook to calculate the unbound
value.

I need to group the report based on the unbound field but in the Sorting and
Grouping wizard the unbound field cannot be selected (only the sourced fields
are visible).

Any ideas how I might achieve this? - I can't build this calculated unbound
field into the original saved query due to the dynamic value being read in
from Excel as the report is displayed.

The value must either be calculated in the query or in the
grouping window.

Create a public function in a standard mofule to perform the
calcuation (instead of in the format event). Be sure to use
arguments to get the two field values or the function will
only be called once.

 

[Andy]

Yep - you are all correct - I kind of guessed the same but hoped for an easy
solution.

Anyway I played with TableDef but finally solved problem by storing the data
plus calculated fields into a temporary table and using the table as source
to the report. Grouping was possible on the calculated fields.