Sort Sequence for Reports

[JoeWharton]

This question regarding Access 2002 running under Windows XP.

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

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

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

Thanks,
Joe Wharton

 

[dhookom]

Is there a reason why you can't add a separate field for this? Your are bending normalization rules by inserting two values into a single field. The presence of your extra character is your extra value and I would place it in a separate field. It doesn't have to show anywhere and it should be easier to maintain than entering extended characters.

Duane
MS Access MVP

 

[JoeWharton]

Duane,
Thanks for the reply. Unfortunately, this database is one I converted from FoxPro and the two tables involved are fairly large, so I would have to remove several hundred entries (the ones I want to sort to the end of the report) from the existing field and put them in a new field. It would be much easier if I could just find a character to insert at the beginning of each entry that would force it to sort to the end.
Joe

 

[dhookom]

I'm not suggesting that you would move values from one field to another. My suggestion would be to add a Yes/No field [SortToEnd]. Then just check the records that you want sorted to the end. You could possibly run an update query to change this value for existing records by searching for your extended character.

Then, you would probably want to remove the extended character from the existing field. When it comes time to sort, use this field first and then the previous field.

Duane
MS Access MVP

 

[JoeWharton]

Duane,

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

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

Thanks again,
Joe

 

[dhookom]

Yes, you could continue to use a single field and set the first sorting level to something like:
=ASC(Left([YourField],1))=160
I would create the new field and then an update query on your table.
UPDATE tblYourTable
SET [SortToEnd]=ASC(Left([YourField],1))=160;

You would need to test this on a backup prior to updating your live data.


Duane
MS Access MVP

 

[JoeWharton]

Duane,

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

left(myfield),1)

What am I doing wrong?

Thanks,
Joe

 

[dhookom]

left(myfield),1)
should be
left(myfield,1)
Create a group header and set the properties to force new page before the section.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JoeWharton]

Duane,

When I tried your suggestion and attempted to view the report a box popped up with the message 'Enter parameter value'. Something still seems to be wrong.

Joe Wharton

 

[dhookom]

Did you use your actual field name? What is your exact error message?
When using an expression in the sorting and grouping dialog, you must prefix the expression with "="
=Left([YourFieldName],1)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.