Sort Sequence for Tables and Reports

  • Thread starter Thread starter JoeWharton
  • Start date Start date
J

JoeWharton

This question regarding Access 2002 running under Windows XP.

I would like to force certain entries in an Access table to the end o
the table when the table is sorted or used in printed reports. I
FoxPro I did this by inserting 'Alt 0160' as the first character of th
entry I wanted to force to the end. Since this character is higher i
the sort sequence than any number or letter this worked fine. Al
entries preceded by this character appeared at the end, sorted on th
second through last characters in the entry. Also, since 'Alt 0160' wa
a character with no printable graphic, it didn't appear when viewin
the table or in printed reports.

For some reason, this doesn't work in Access. I've tried many differen
characters in the Character Map, as well as 'Alt 0160' and no matte
which one I try, entries preceded by one of these characters sort t
the beginning.

Does anyone know of a way to force entries to sort to the end of
table, preferably with an unprintable character or one that isn't to
obvious, such as a period?

Thanks,
Joe Wharto
 
Since you are going to the trouble to manually add these in anyway, you could just create
another field, perhaps called PrintAtEnd as a True/False field, and sort on this field
prior to sorting on your current field. It wouldn't have to be printed, just sorted on.
 
Wayne,

It hadn’t occurred to me to do two sorts. I’m not a very experience
user so I hope I can figure out how to implement your suggestion.

One other question – In order to avoid the work of creating anothe
field and entering a character in all the entries I want sorted to th
end, would it be possible to use an IIf statement that would do the tw
sorts, with one sort occurring on the entries where the first characte
of the field is Alt 0160 and the other occurring on entries where th
first character is anything else? If I could do this I wouldn’t have t
create a new field and make several hundred entries in it. If this i
possible, how should the IIf statement read?

Thanks again,
Jo
 
The 2nd sort is easy. In the query design grid, you order the fields you want sorted from
left to right, setting the sort order to ascending or descending as desired. The field to
the left is sorted first.

As far as using an IIF statement to fill in this extra field, it would depend on what the
data looks like and whether you could write something that can differentiate between the
ones you want printed first and the ones you want printed last. However, if you can do
that then you may be able to get by without the extra field. Also, you wouldn't need to
fill something in for each record in this extra field, just the ones you want printed last
(or first if that's fewer records and reverse the sort order for that field).
 
Wayne,

Thanks for the help - I got that working ok. Now I need to know how t
write a statement to put in the Sorting and Grouping box that wil
force a new group and skip to a new page when the leftmost character o
a field changes. I tried the following and it didn't work:

left(myfield),1)

What am I doing wrong?

Thanks,
Jo
 
Back
Top