Hello.
I wanted to let you know that I came up with a workaround for this
issue.
It seems that this scenario doesn't work because it involves grouping
(with a header or footer) on an expression which can result in True
(-1), False (0) or Null IF this is done on the third grouping level
(or later). I had the same error when I added a Yes/No field in the
dicPaycodes table which indicated if Category2 is 'Contracts' or not
and used this field to group on.
What I had to do was to add a text field (length 5) in dicPayCodes.
This field
(Cat2Contracts) has value "Yes" if dicPayCodes.Category2='Contracts'
and
"No" otherwise. My report(s) are now selecting this field and
grouping by
it instead of the expression "dicPayCodes.Category2<>'CONTRACTS'.
This will require us to update this field when the value for Category2
is
changed (or set for a new record). This will be done via the
AfterUpdate()
method for the Category2 control on the PayCodes form (where users add
or
edit records).
If you come up with a possible solution for the original problem,
please let
me know.
Thanks,
Ahsan
ahaque38@hotmail.com (ahaque38@hotmail.com) wrote in message news:<a25d91e3.0407080606.589154@posting.google.co m>...[color=blue]
> Hello.
>
> The "Sorting and Grouping" dialog is using the calculated field
> "CatContractsYN". When the query is run directly, it results in 1 or
> 2 groups for all the divisions (with 2 groups for the first division
> output on the report). However, I understand your note about a
> possible third Null outcome. I'll check into how to ensure 2 or less
> outcomes...
>
> The thing to note is that the same approach for the Plan and
> Department level reports works.
>
> The lines of data which show up in the 2 groups on the first page and
> the 1 group on the second page of the report are all supposed to be in
> the first group (first dept-division combination for
> Category2<>'CONTRACTS'). Also, the totals for the first group, second
> group and overall division total are all correct for the first
> dept-division combination. It is somehow just not putting the details
> into the correct groups (based on the calculated field
> "CatContractsYN") when a header or footer (even if it's blank) is
> specified for this group.
>
> Thanks for any more input.
>
> Ahsan
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:<40ec13d9$0$30014$5a62ac22@per-qv1-newsreader-01.iinet.net.au>...[color=green]
> > The query conatins a calculated field named CatContractsYN.
> >
> > Try using that name in the Sorting'n'Grouping dialog instead of the
> > expression.
> >
> > BTW, you will get 3 groups of records. The field is on the outer side of the
> > join, so even if it is a required field it can have 3 possible values in the
> > query: Null, True, and False.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users -
http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > <ahaque38@hotmail.com> wrote in message
> > news:a25d91e3.0407070707.160fa0ea@posting.google.c om...[color=darkred]
> > >
> > > Using A2K SP3, I am having the following problem with a report using
> > > "Sorting and Grouping".
> > >
> > > I have recently added a grouping in the reports for
> > > "Category2<>'CONTRACTS'".
> > >
> > > I have reports at the plan (overall totals), department and division
> > > levels which have sorting and grouping implemented with this new
> > > grouping. The first two reports work as expected. However, for the
> > > division level report, the Access report is not grouping as expected.
> > > See details below.
> > >
> > > For the report which does not work, the source is an SQL statement as
> > > follows...
> > >
> > > TRANSFORM Sum(ytdIA2004.Amount) AS [The Value]
> > > SELECT mapTransfers.Department, mapTransfers.Division,
> > > dicPayCodes.Category2<>'CONTRACTS' AS CatContractsYN,
> > > dicPayCodes.Category2, Sum(ytdIA2004.Amount) AS [Year To Date]
> > > FROM (ytdIA2004 LEFT JOIN dicPayCodes ON ytdIA2004.[PayCode#] =
> > > dicPayCodes.[PayCode#])
> > > LEFT JOIN mapTransfers ON (ytdIA2004.[IDX Program] =
> > > mapTransfers.[IDX Program]) AND
> > > (ytdIA2004.[IDX Billing Area] = mapTransfers.[IDX Billing Area]) AND
> > > (ytdIA2004.[IDX Division] = mapTransfers.[IDX Division])
> > > WHERE (ytdIA2004.[Income Type] = 'Payments' OR ytdIA2004.[Income Type]
> > > = 'Refunds') AND
> > > ytdIA2004.[PayCode#] Is Not Null AND ytdIA2004.[Date] <= #05/01/2004#
> > > GROUP BY mapTransfers.Department, mapTransfers.Division,
> > > dicPayCodes.Category2<>'CONTRACTS', dicPayCodes.Category2
> > > PIVOT Format([Date],'mmm') In
> > > ('Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb',' Mar','Apr','May','Jun');
> > >
> > > The Access report has the following "sorting and grouping"
> > > specifications:
> > >
> > > Department Ascending (with no group header or footer)
> > > Division Ascending (with no group header but a group footer)
> > > CatContractsYN Ascending (with both a group header and group footer)
> > > Category2 Ascending (with no group header or footer)
> > >
> > > For all of the above, the other settings are as follows
> > > Group On: Each Value
> > > Interval: 1
> > > Keep Together: No
> > >
> > > I am expecting to see multiple records with Category2<>'CONTRACTS'
> > > (which evaluates to "-1") in the first group and one record with
> > > Category2='CONTRACTS' (which evalueates to '0') in the second group
> > > for "CatContractsYN". This works for the plan and department level
> > > reports.
> > >
> > > However, for this division level report, I see only one record in the
> > > first group (for the first department & division on the report) and
> > > one record (which is not for Category2='CONTRACTS') in the second
> > > group. In addition, if I go to page 3 of the report, I get multiple
> > > "No current record" messages. Somehow, the report's source data is not
> > > being matched up correctly to the report definition.
> > >
> > > If I take out the header AND footer for the CatContractsYN group, the
> > > report groups correctly. However, putting in a header or footer for
> > > this group results in the problem.
> > >
> > > I have tried running the source query independently and it seems to
> > > group and sort as expected. The issue seems to be with the report
> > > design.
> > >
> > > I would appreciate any ideas.
> > >
> > > Thanks,
> > > Ahsan[/color][/color][/color]
