How To Sort Records Alphabetically BUT Exclude Just One Record

[nicebasic]

I would like to have a query for ASP Classic to be applied on an Access database.

1. Imagine that we have a table with the name of "Country".
2. Imagine that we have a column in "Country" that has this name: "CityMajor"
3. Imagine that we wish to have a query sort the items in this column, but with one exception: One of the records should stand on top of the other records while other records are sorted alphabetically.
4. An example could clarify this point. For example, we have the list of the following Major Cities in our list (Unsorted Alphabetically):

Paris
London
Tehran
Rome
New York
Berlin
Tokyo

5. Now we wish to have this list returned and sorted alphabetically, but with the exception that "New York" stands on top of the other cities. But the other items should be in alphabetical order:

New York
Berline
London
Paris
Rome
Tehran
Tokyo

How can I accomplish this query in ASP Classic for an Access Database?

Thank you in advance for any help in this regard.

[ADezii]

1. If you wish to simply generate a List, then pass to the following Function the Name of the City you want at the Top of the List. All other Cities will follow alphabetically. For the sake of brevity and simplicity, the Code does not include Error Checking, makes the major assumption that the [City Major] Field is REQUIRED (cannot be NULL), and only minimal Validation is performed as regards the String passed to the Function.
   

   Public Function fSpecialSort(strTop As String)
   Dim MyDB As DAO.Database
   Dim rstSort As DAO.Recordset
   Dim strSQL As String
   
   'See if we have actual Records
   If DCount("[CityMajor]", "Country") = 0 Then Exit Function
   
   'Is strTop a City contained in the [CityMajor] Field?
   If DCount("*", "Country", "[CityMajor] = '" & strTop & "'") = 0 Then
     MsgBox strTop & " does not exist in the Country Table!", vbExclamation, "Invalid City Name"
       Exit Function
   End If
       
   strSQL = "SELECT [CityMajor] FROM Country ORDER BY [CityMajor];"
   
   Set MyDB = CurrentDb
   Set rstSort = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
   
   Debug.Print strTop      'PRINT City to be at the Top of List
   
   With rstSort
     Do While Not .EOF
       If ![CityMajor] <> strTop Then      'EXCLUDE City at the Top, do not repeat
         Debug.Print ![CityMajor]
       End If
         .MoveNext
     Loop
   End With
   
   rstSort.Close
   Set rstSort = Nothing
   End Function

2. Sample Function Call ('New York' on Top)
   

   Call fSpecialSort("New York")

   Sample OUTPUT:
   

   New York
   Berlin
   London
   Paris
   Rome
   Tehran
   Tokyo

[nicebasic]

Thank you for you clean Code. But I can't run it.

Thank you for your clean code.
Since I'm not as professional as you, I find your code rather difficult. I pasted your code in a New VB Form, but it stopped on this line:

If DCount("CityMajor", "Country") = 0 Then Exit Function

The Function "DCount" was not known to the program and was highlighted by the IDE. The error code was as follows:

Sub or Function not defined.

I added a Reference to "Microsoft DAO 3.6 Object Library", but it didn't help.

How can I run your code in a project FROM SCRATCH ?

By the way, I'm going to run this solution for an ASP Classic program. It might be a little different to apply VB SQL queries to ASP Classic code because of minor syntax difference.

Your solution, seems to be a really good one. But, isn't there an SQL command to help us more about this. I mean, if we have to loop through all the records and EXCLUDE one record, it might be rather slow for large Databases and might occupy system resources. As the code is to be run on the Web, would it be possible for you to help me find an SQL Query for this?

Thank you very much for you kind help and support.

[colintis]

DCount is part of the function contained in Access, so it should be working. Are you actually running the code that is not in the Access VBA environment?

[nicebasic]

I tested your code in the VB environment.

I tested your code in Visual Basic 6.0 IDE (and not in the Access VBA IDE) and tried to run it. As you mentioned, the DCount Function does not work in the Visual Basic 6.0 Environment.

I haven't used VBA in Access up to now.

I'll give it a try, though it's rather difficult for me. I'm not used to it.

Thank you very much for your kind attention.

[Delerna]

Sorry for butting in but when I need to do this I generally arrange my query so it adds a calculated field for sorting by "sort classes", as I like call it. So the query would return something like this

SrtClass   City
2          Paris
2          London
2          Tehran
2          Rome
1          New York
2          Berlin
2          Tokyo

Now all I would do is

SELECT City
FROM TheQueryWithTheSrtClass
ORDER BY SrtClass,City

in the query for my display of the cities

[nicebasic]

I've already done this by adding and Extra Sort Field.

You talked of "Sort Classes". Maybe you look at me as a professional. I'm so new to SQL Queries and just know some easy SQL Statement. You seem to be a pro. I don't understand the two phrases (calculated field) & (sort class):

calculated field for sorting by "sort classes"

I have already made use of a kind of "Sort Field" to solve this problem. But, I've got another Database that's fixed and not mine. I have to change the code that accesses that database. I can't add anything to the specified database. That's why, I have to modify my code.

By the way, is it possible to have "calculated field" and "sort classes"? Are they some sort of "Temporary Fields" that are created on the fly and can be removed easily?

Thank you very much.

[ADezii]

To make things clearer for you, I created a Demo that populates a List Box with the desired results. There is no reason why the code should not work, unless you are using an earlier Version of Access, in which case there is a work-a-round. Download the Attachment.

[nicebasic]

Thank you very much, ADezii.

This is the best thing you did. I needed a DEMO, since I haven't done VBA programming in Acesss. You're great.

Thank you a million times.
I really appreciate your help.

[Delerna]

Just to clarify for you the meaning of

(calculated field) & (sort class):


Sort class is a phrase of my own invention.
I am referring to the numbers in the sample data. When the data is sorted it will sort all the 1's first and then all the 2's last
So I effectively have a way of splitting your city data into 2 (or more) groups for sorting purposes



A calculated field is just a field you create within a query. It does not exist in any table.
So the Cls field in my posted data is a calculated field and would possible look something like for your sample
I am being simple in this example

SELECT iif([city]="New York",1,2) as Cls,
    City
FROM theTable

another example of a calculated field as, say a record for a customer order.
The customer buys a certain quantity of an item for a certain unit cost.

In the table you would have a Qty and a Cost field. But you wouldn't have a total field because you would calculate that in the query used by any forms or reports.

So, total in this query is a calculated field, it dosn't exist in the orders table

SELECT ItemNo,Qty,Cost,Qty*Cost as Total
FROM tblOrders

[ADezii]

You are quite welcome, but I actually think that Delerma's solution in Post #6 is much more efficient and definitely simpler. The choice, of course, is yours.

[ADezii]

You are quite welcome, but I actually think that Delerma's solution in Post #6 is much more efficient, practical, and definitely simpler, as long as you do not mind periodically changing Values (2) in the [SrtClass] Field. The choice, of course, is yours.

[ADezii]

You are quite welcome, but I actually think that Delerna's solution in Post #6 is much more efficient, practical, and definitely simpler, as long as you do not mind periodically changing Values (2) in the [SrtClass] Field. The choice, of course, is yours.

[ADezii]

Sorry for all the Dups, not sure what exactly happened.

[Delerna]

Yes as ADezii highlights, this method cannot always work

SELECT iif([city]="New York",1,2) as Cls, 
    City 
FROM theTable

the above code is meant for illustration purposes. In practice you need somthing more elaborate than that if you need to dynamically allow different cities to be at the top


Perhaps

SELECT iif([city]=Forms!FormName.txtTopCity,1,2) as Cls, 
    City 
FROM theTable

as a slightly better example