A tricky little pre-sort query followed by a JOIN of that query to your real data might do what you wanted.
Here is the overall idea.
Step 1: Build a query that has fields as follows in the query grid.
Column 1
Field = SrtFld1: IsNumeric(Left([DeptCode],1))
Sort order = Descending
(and uncheck the box that says to show it)
In English, created an alias-named field that is based on a nested pair of functions that tests the first character of the department code for being numeric or not.
Column 2
Field = DeptCode
Sort order = Ascending
(and leave the "show" box checked.)
Save it. Call it something like 'SpecialSortQuery'
Step 2: Write the query that you want based on THIS query joined through DeptCode to the rest of your data. In my example, I just call it DeptTable for lack of a better name.
The SQL of this query might be as simple as
SELECT * FROM SpecialSortQuery INNER JOIN DeptTable ON DeptTable.DeptCode = SpecialSortQuery.DeptCode ;
Step 3: Write your report or whatever else you needed based on this query as your data source - rather than directly using the table. If you really did use SELECT * then you have all the data anyway.
The theory of this is that if you got a numeric digit as the start of the code, the IsNumeric returns True which is numerically equal to -1. But if you got text, IsNumeric returns False, which is 0. So sort this part first to put leading numbers and digits in the order you want, then sort the remainder according to normal ordering within the subclasses.
Play with it. Even if I got the silly sorts bass-ackwards, which is incredibly common for me, this concept is how you do what you wanted to do.
[This message has been edited by The_Doc_Man (edited 04-03-2002).]
