I just learned that ~ does not come after z when sorting - I thought the ASCII chart was 'my friend'.

Background:
F/E - Access, B/E SQL Server
Developing Sales Budget and each Sales Agent has a category "Other" for sales not to a specific customer.
So, I created customer records with the names beginning with ~Other (Budget) - {the Sales Agent's Name}, thinking that ~ came after z.

When we ran the Access report ~Other came before customer names (beginning with letters and numbers)
- The sorting is performed in the Access report.

Googling around...
I could not find information about the sort 'character precedence'.
I found information that SQL sorting is based on the Collation, ours is SQL_Latin1_General_CP1_CI_AS.
- Did some googling on that and understand the gist of it, didn't delve further at this time.

The immediate issue: Find a character that follows z when sorting in Access (queries and reports).
The goal - find the equivalent of the ASCII chart for (a) Access sorts and (b) SQL sorts [I started using Pass Thru's and want to use them more and more].

You could always add a group in your report where instead of grouping on a field, group it on an expression something like:

IIf([Category] ='Other', 1, 0)

Sort by that grouping. Those that are not "Other" will evaluate to 0, and then any that are "Other" will evaluate to 1. Since 1 coems after 0, the Others should come last.

If you are doing this in the SQL Server side of things, instead of IIf() use a Case statement.

Mike

Hi Mike,

Thanks. I thought about that one. You might say that it's 'in my hip pocket'. The downside is that it accomplishes the immediate situation - this report. But, then I would need to 'patch' for every report. It would be easier to prefix the name with a character that naturally sorts.

Have a fun day.

One useful character I use to change sorting filenames in Windows is the blank. It sorts before the alphanumerics where the space sorts after.
It is also very subtle since no glyph is displayed. The tiny offset is the only indication it is there.

I have not tried it in Access but I expect it would be the same.

To enter it hold down ALT and enter 0160 on the numeric keypad.

You can use a different collation (sorting rule) in your query than the default for SQL Server, or you can set the whole thing in SQL Server (say if you used a stored procedure). Works great if you have access to the server instance etc. What is the collation on your instance of SQL Server set to?