Custom Sort Order in Access

[christy726]

To sort data in the Reports Design View, I know we can use the "Sorting and Grouping" function where it can sort data in ascending or descending order. But what if I want to sort in a specific order?

For example, the field name is "Region". I have records showing "East", "North", "South", and "West". Is there a way that I can sort by "North", "South", "East", and "West" instead? If I use ascending order, it won't work.

Please let me know. Thanks for your help!

 

[giacomo]

Typically when I need a custom sort I add a sortOrder field to my table:

<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMainLT" border-left=1px solid colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInnerLT" align=Left >tblRegion : Table</TD><TD CLASS="AccTBInnerLT" align=right >Access 2002/XP</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElemLT" align=left >regionId</TD><TD CLASS="AccDataElemLT" align=left >regionName</TD><TD CLASS="AccDataElemLT" align=left >sortOrder</TD></TR><TR ><TD CLASS="AccDataElemLT" >1</TD><TD CLASS="AccDataElemLT" >North</TD><TD CLASS="AccDataElemLT" >1</TD></TR><TR ><TD CLASS="AccDataElemLT" >2</TD><TD CLASS="AccDataElemLT" >East</TD><TD CLASS="AccDataElemLT" >3</TD></TR><TR ><TD CLASS="AccDataElemLT" >3</TD><TD CLASS="AccDataElemLT" >South</TD><TD CLASS="AccDataElemLT" >2</TD></TR><TR ><TD CLASS="AccDataElemLT" >4</TD><TD CLASS="AccDataElemLT" >West</TD><TD CLASS="AccDataElemLT" >4</TD></TR><TR ><TD CLASS="tpkrow" colSpan = 3 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>


alternatively, since it's only four values you could also use an iif function to get it to sort correctly:

<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 2 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><font color="White">Query1 : Select Query</font></TD><TD CLASS="AccTBInner" align=right >Access 2002/XP</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElem" colSpan= 2 >SELECT
tblRegion.regionName

FROM
tblRegion
ORDER BY
IIf([regionName]="North",1,IIf([regionName]="South",2,IIf([regionName]="East",3,4)));
</TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=left >regionName</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >

</TD><TD CLASS="AccDataElem" >North</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >South</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >East</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >West</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 2 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" >

</TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" >

</TD><TD CLASS="AccIEOnlyInnerLeft" > of 4</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 2 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data150402248" CLASS="AccInfoBarInnerRight" >Query1</TD><TD ID="Info150402248" CLASS="AccInfoBarData" >Select Query, Record Count : 4</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 2 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

hth,
Giacomo