Using calculated controls as a grouping criteria

I have a report that allocates a cumulative
liability/asset based on the cumulative components of the
liability. The problem is that when the liability becomes
an asset (or goes to 0) our partner wants the cumulative
components to be reset so that the liability/asset
reported between us is more responsive to changes in the
components. If I could group on a calculated control in
the report that indicates when the cumulative liability
asset is positive or negative this would be fairly simple
but I can when I enter the control name as a grouping
level the report does not recognize this as a field. I
have tried using a expression that is based on the same
formula as the indicator control but the when the report
runs I get a "comma" error and the report runs ignoring
the grouping for this level. Any suggestions on how to
use expressions in grouping are welcomed.

Anonymous,

One approach is to make your indicator field as a calculated field
within the query that the report is based on, and then use this directly
in the report's Sorting and Grouping. The other, more what you have
tried, is to use a calculated expression within the Sorting and
Grouping. I am not sure why your approach is not working, without
seeing the expression you are trying to use. But the first thing to
check is that you put a = in front of the expression :-)

--
Steve Schapel, Microsoft Access MVP

wrote:
I have a report that allocates a cumulative
liability/asset based on the cumulative components of the
liability. The problem is that when the liability becomes
an asset (or goes to 0) our partner wants the cumulative
components to be reset so that the liability/asset
reported between us is more responsive to changes in the
components. If I could group on a calculated control in
the report that indicates when the cumulative liability
asset is positive or negative this would be fairly simple
but I can when I enter the control name as a grouping
level the report does not recognize this as a field. I
have tried using a expression that is based on the same
formula as the indicator control but the when the report
runs I get a "comma" error and the report runs ignoring
the grouping for this level. Any suggestions on how to
use expressions in grouping are welcomed.

Steve,

Thanks for responding. The formula I tried is fairly
simple: =iif(PdayImbal0,"POS","NEG") I have this as a
calculated control,[pdayindicator] and tried grouping on =
[pdayindicator]. When I used this approach and ran the
report it acted as though [pdayindicator] was a parameter
in a query, asking for a value. I rechecked that
[pdayindicator] was named correctly. I also tried a
fully qualified reference. The reason i did not try a
calculated field in a query is that the pdayimbalance is
a running sum of three fields in the query. I need a
trigger for when the sign of the running sum changes to
change the formula of a different calculated control
which is also a running sum which I want to reset
whenever the sign of [Pdayimbal] changes.

-----Original Message-----
Anonymous,

One approach is to make your indicator field as a
calculated field
within the query that the report is based on, and then
use this directly
in the report's Sorting and Grouping. The other, more
what you have
tried, is to use a calculated expression within the
Sorting and
Grouping. I am not sure why your approach is not
working, without
seeing the expression you are trying to use. But the
first thing to
check is that you put a = in front of the expression :-)

--
Steve Schapel, Microsoft Access MVP

wrote:
I have a report that allocates a cumulative
liability/asset based on the cumulative components of
the
liability. The problem is that when the liability
becomes
an asset (or goes to 0) our partner wants the
cumulative
components to be reset so that the liability/asset
reported between us is more responsive to changes in
the
components. If I could group on a calculated control
in
the report that indicates when the cumulative
liability
asset is positive or negative this would be fairly
simple
but I can when I enter the control name as a grouping
level the report does not recognize this as a field.
I
have tried using a expression that is based on the
same
formula as the indicator control but the when the
report
runs I get a "comma" error and the report runs
ignoring
the grouping for this level. Any suggestions on how
to
use expressions in grouping are welcomed.
.

Anonymous,

Ok, thanks for the further explanation.

As far as I know, you can't refer to the name of a calculated control
directly in the Sorting and Grouping. You need to refer to fields.
Therefore, you could put =IIf(PdayImbal0,"POS","NEG") into the
Field/Expression of the Sorting and Grouping dialog. But on the basis
of the rest of your description, I suspect this will not serve your
purposes. I can't see how Sorting and Grouping can relate to a running
sum. Even if the above idea works, all it will do is put all your POS
records together and all the NEG records together. In other words, it
seems like you might be using the wrong tool for the job. If you want
something to "happen" based on a record by record checking process, you
might need instead to look at some VBA code on the Format event of the
relevant report section. Sorry, on the basis of what I know so far, I
can't be more specific that that.

--
Steve Schapel, Microsoft Access MVP


wrote:
Steve,

Thanks for responding. The formula I tried is fairly
simple: =iif(PdayImbal0,"POS","NEG") I have this as a
calculated control,[pdayindicator] and tried grouping on =
[pdayindicator]. When I used this approach and ran the
report it acted as though [pdayindicator] was a parameter
in a query, asking for a value. I rechecked that
[pdayindicator] was named correctly. I also tried a
fully qualified reference. The reason i did not try a
calculated field in a query is that the pdayimbalance is
a running sum of three fields in the query. I need a
trigger for when the sign of the running sum changes to
change the formula of a different calculated control
which is also a running sum which I want to reset
whenever the sign of [Pdayimbal] changes.