Problem with Reports and Queries

[John D in London, England]

I have several select queries which extract various different sets of records
from a database. The queries all include a sort order specification. I also
have several reports which can use any of the queries. The OnOpen method of
each report receives the name of the required query in openArgs and stores it
in RecordSource.

Most of the reports work fine and list the records in the order specified in
the query. But one particular report gets the order of records wrong. It
lists them in sequence of their autoincrementing record number - i.e.
presumably in the order they are stored in the table. Has anyone seen a
problem like this? - and do you have any solutions.

 

[Marshall Barton]

I have several select queries which extract various different sets of records
from a database. The queries all include a sort order specification. I also
have several reports which can use any of the queries. The OnOpen method of
each report receives the name of the required query in openArgs and stores it
in RecordSource.

Most of the reports work fine and list the records in the order specified in
the query. But one particular report gets the order of records wrong. It
lists them in sequence of their autoincrementing record number - i.e.
presumably in the order they are stored in the table. Has anyone seen a
problem like this? - and do you have any solutions.

Only trivial reports can sort their records in the record
source query. Sorting in reports needs to be specified in
the report's Sorting and Grouping window (View menu).

 

[John D in London, England]

Thanks very much Marshall, that is most helpful.

Do you by any chance know what constitutes a trivial report? The report that
is giving me trouble is certainly more complicated than the ones that work.
In particular one of the sort keys specified in the query (ForeName) is not
used directly on the report,. It is only used in an expression which merges
it with another field. Could that be my problem?

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

not sure I agree with that

 

[Tom Wickerath]

not sure I agree with that

Only trivial reports can sort their records in the record
source query. Sorting in reports needs to be specified in
the report's Sorting and Grouping window (View menu).

 

[John Spencer]

Try including ForeName as a separate field in the underlying query. Then
you should be able to use it in the report's Sorting and Grouping.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Thanks very much Marshall, that is most helpful.

Do you by any chance know what constitutes a trivial report? The report that
is giving me trouble is certainly more complicated than the ones that work.
In particular one of the sort keys specified in the query (ForeName) is not
used directly on the report,. It is only used in an expression which merges
it with another field. Could that be my problem?

My definition of a trivial report is one that does not use
any grouping and does not use any aggregate functions
(Count, Sum, etc). IOW, a trivial report is not much more
than a dump of the record source table/query.

To specify a sort/group in the report's Sorting and Grouping
list, the field must be in the record source query's field
list. Either do as John said or, if you really want to sort
on the combined value, specify the calculated field.