'Sorting By' on Reports

[Jacob Frankham]

Hi

I have a query which is sorting its results by certain values (as opposed to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake

 

[Fredg]

The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

 

[Duane Hookom]

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP

The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Hi

I have a query which is sorting its results by certain values (as

opposed

to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake

 

[Jacob Frankham]

PERFECT

Thanks Guys !!

Jake

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP

The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.

SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Hi

I have a query which is sorting its results by certain values (as

opposed

to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top

of
the

query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake

 

[Marshall Barton]

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortotder field, but very
clever none the less.

 

[Douglas J. Steele]

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortotder field, but very
clever none the less.

Just don't ask him how to do multiplication in a query! <g, d&r>

 

[Marshall Barton]

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortorder field, but very
clever none the less.

Just don't ask him how to do multiplication in a query! <g, d&r>

Good shot Doug, but we don't have to ask. He announces it
without any prompting. ;-)

For those that weren't there,

Product(field) = Exp(Sum(Log(field)))

Really, it's just some simple math <g, d & r right with
you>

 

[Duane Hookom]

You guys are picking on an almost grandpa so try to show a little more
respect.

--
Duane Hookom
MS Access MVP

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortorder field, but very
clever none the less.

Just don't ask him how to do multiplication in a query! <g, d&r>

Good shot Doug, but we don't have to ask. He announces it
without any prompting. ;-)

For those that weren't there,

Product(field) = Exp(Sum(Log(field)))

Really, it's just some simple math <g, d & r right with
you>

 

[Marshall Barton]

You guys are picking on an almost grandpa so try to show a little more
respect.

Oh? OK, that can be a trying experience so we'll cut you a
little slack this time. But, looking back in this thread,
you started it with your clever code so you can't be too
distracted ;-)

 

[Jacob Frankham]

Yes, v clever code Duane, thanks for that

Thanks also to Freg

Doug, keep up the Math

Duane, congratulations.

Does that cover everything?!?!?!

Jake !

 

[John Spencer (MVP)]

Nice one, Duane. How does it handle Nulls?

Perhaps by using "AdvisoryLowMediumHigh",Nz([Risk],"") and Descending sort?

Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP

The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Hi

I have a query which is sorting its results by certain values (as

opposed

to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake

 

[Duane Hookom]

Thanks guys. Ask me some time about my theory of "Cartesian Problem Solving"
techniques.