Sorting Crosstab in Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a report based on a crosstab query. I use an unbound calculated field to weight the results of the query. I want to sort the report on the calculated field, but can't figure out how to do it. When i go to grouping and sorting I only get the elements of the crosstab query. Any suggestions? Thanks ahead of time.

Bob
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the calculation formula you have in the unbound control in the
Expression column of the Sorting & Grouping dialog box; then change
its Sorting column to whatever you want.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBVxaIechKqOuFEgEQIILgCeJ44jeaZzf81bdU+wfgQKkKI0HPMAn19f
qP71hV9ViQ9ZotxwENtLOWcF
=KtNL
-----END PGP SIGNATURE-----
 
Thanks. That worked great. However, my explanation was incomplete. I also have a DLookup field that I would like to add to the weighted result. I added the Dlookup fucntion to the expression field in Grouping and Sorting but it didn't work. I got the following error, Microsoft Jet doesn't recognize " as a valid field name or expression. I know the Dlookup is functioning properly because I can open the report if I don't try to sort by the calcualted field. Any more tips? Thanks again

----- MGFoster wrote: ----

-----BEGIN PGP SIGNED MESSAGE----
Hash: SHA

Put the calculation formula you have in the unbound control in th
Expression column of the Sorting & Grouping dialog box; then chang
its Sorting column to whatever you want

- -
MGFoster:::mgf00 <at> earthlink <decimal-point> ne
Oakland, CA (USA

-----BEGIN PGP SIGNATURE----
Version: PGP for Personal Privacy 5.
Charset: nocon

iQA/AwUBQBVxaIechKqOuFEgEQIILgCeJ44jeaZzf81bdU+wfgQKkKI0HPMAn19
qP71hV9ViQ9ZotxwENtLOWc
=KtN
-----END PGP SIGNATURE----


Bob Ewers wrote
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps, instead of having the DLookup() function in a report control
(and the Sorting/Grouping dialog box), you put it in the report's
RecordSource query, in the SELECT clause. That way you'll get the
DLookup() value as part of the report's data and you can include it in
the calculation as a value (column name) instead of the complete
DLookup() function. E.g.:

SELECT This, That,
DLookup("Amount","Sales","SalesDate = O.SalesDate") As SalesAmt
FROM Orders As O
.....

You'd use the column SalesAmt in you calculation.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBbXFIechKqOuFEgEQLcjwCaAnwEgVdmJ5VCL1AsD6YnlMKpj5IAn01W
0i+baU23jz0U+kqJmqy/n2hf
=7EO/
-----END PGP SIGNATURE-----
 
Back
Top