473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.
Sep 20 '10 #1

✓ answered by 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.

20 4436
ADezii
8,834 Expert 8TB
  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.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fSpecialSort(strTop As String)
    2. Dim MyDB As DAO.Database
    3. Dim rstSort As DAO.Recordset
    4. Dim strSQL As String
    5.  
    6. 'See if we have actual Records
    7. If DCount("[CityMajor]", "Country") = 0 Then Exit Function
    8.  
    9. 'Is strTop a City contained in the [CityMajor] Field?
    10. If DCount("*", "Country", "[CityMajor] = '" & strTop & "'") = 0 Then
    11.   MsgBox strTop & " does not exist in the Country Table!", vbExclamation, "Invalid City Name"
    12.     Exit Function
    13. End If
    14.  
    15. strSQL = "SELECT [CityMajor] FROM Country ORDER BY [CityMajor];"
    16.  
    17. Set MyDB = CurrentDb
    18. Set rstSort = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    19.  
    20. Debug.Print strTop      'PRINT City to be at the Top of List
    21.  
    22. With rstSort
    23.   Do While Not .EOF
    24.     If ![CityMajor] <> strTop Then      'EXCLUDE City at the Top, do not repeat
    25.       Debug.Print ![CityMajor]
    26.     End If
    27.       .MoveNext
    28.   Loop
    29. End With
    30.  
    31. rstSort.Close
    32. Set rstSort = Nothing
    33. End Function
    34.  
  2. Sample Function Call ('New York' on Top)
    Expand|Select|Wrap|Line Numbers
    1. Call fSpecialSort("New York")
    Sample OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. New York
    2. Berlin
    3. London
    4. Paris
    5. Rome
    6. Tehran
    7. Tokyo
Sep 20 '10 #2
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 21 '10 #3
colintis
255 100+
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?
Sep 21 '10 #4
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.
Sep 21 '10 #5
Delerna
1,134 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. SrtClass   City
  2. 2          Paris
  3. 2          London
  4. 2          Tehran
  5. 2          Rome
  6. 1          New York
  7. 2          Berlin
  8. 2          Tokyo
  9.  
Now all I would do is
Expand|Select|Wrap|Line Numbers
  1. SELECT City
  2. FROM TheQueryWithTheSrtClass
  3. ORDER BY SrtClass,City
  4.  
in the query for my display of the cities
Sep 21 '10 #6
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.
Sep 21 '10 #7
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Sort.zip (16.6 KB, 123 views)
Sep 21 '10 #8
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.
Sep 21 '10 #9
Delerna
1,134 Expert 1GB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]="New York",1,2) as Cls,
  2.     City
  3. FROM theTable
  4.  
  5.  

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
Expand|Select|Wrap|Line Numbers
  1. SELECT ItemNo,Qty,Cost,Qty*Cost as Total
  2. FROM tblOrders
  3.  
Sep 21 '10 #10
ADezii
8,834 Expert 8TB
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.
Sep 21 '10 #11
ADezii
8,834 Expert 8TB
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.
Sep 21 '10 #12
ADezii
8,834 Expert 8TB
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.
Sep 21 '10 #13
ADezii
8,834 Expert 8TB
Sorry for all the Dups, not sure what exactly happened.
Sep 21 '10 #14
Delerna
1,134 Expert 1GB
Yes as ADezii highlights, this method cannot always work
Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]="New York",1,2) as Cls, 
  2.     City 
  3. FROM theTable 
  4.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]=Forms!FormName.txtTopCity,1,2) as Cls, 
  2.     City 
  3. FROM theTable 
  4.  
as a slightly better example
Sep 22 '10 #15
Thank you very much, Delerna.

You've been so kind in this case. I thank you so much for being so informative.

Actually, you seem to be a really professional programmer. Your method is a brilliant and systematic one. There is a problem with me, however. I'm not as knowledgeable as you are in this case.

I wonder if it's possible to apply your commands to an ASP Classic code. Since the syntax of SQL Queries in ASP Classic is different from Access VBA, I'll have to focus on this matter more.

Thank you very much indeed for your kind attention.

I warmly appreciate your help, Delerna.
Sep 22 '10 #16
ADezii
8,834 Expert 8TB
I took the liberty of creating a 2nd Demo based solely on Delerna's approach, which again is more efficient. The Method, I feel is essentially foolproof in that:
  1. The Combo Box will only display 'Unique' Cities within the Country Table.
  2. Select a City from the Combo Box, then click on the Command Button to Open a Query based on Delerna's Logic. There need be no Validation on the City Name with this approach.
  3. Should the Combo Box not contain a Value (NULL) when the Command Button is clicked, the User is warned of this via a Message Box, focus is now set to the Combo Box, and it is Dropped Down for another try.
  4. Download Sort2.zip and you'll see exactly what I mean.
@Delerna - I hope you did not mind that I used your Code. It's simply that yours is a much better approach, and I would prefer to see it used in this case. I am definitely not trying to 'Step on Your Toes'. If you feel as though my adaptation can be further improved, please feel free to do so. Thanks.
Attached Files
File Type: zip Sort2.zip (19.0 KB, 97 views)
Sep 22 '10 #17
Delerna
1,134 Expert 1GB
Adezii
I have no problems with that at all.

I come here to share what I have learned as well as to learn from what others share
Sep 22 '10 #18
Delerna
1,134 Expert 1GB
Nicebasic

I wonder if it's possible to apply your commands to an ASP Classic code. Since the syntax of SQL Queries in ASP Classic is different from Access VBA, I'll have to focus on this matter more.
Classic asp is starting to become "old school" but the answer is yes. I still use classic asp connecting to MSSQL server (access is not much different) and querying for data to display on intranet web pages for almost everything I do.
What, exactly, do you need to know? I will help if I can
Sep 22 '10 #19
This is really great, ADezii.

You made a great demo in your excellent Sort2 project.

Thank you again and again for being so kind.
Sep 23 '10 #20
I have learnt a lot thanks to ADezii's and YOUR contribution to THIS POST.

I still cannot apply this code to my ASP Classic project. Since you're familiar with ASP Syntax, I wonder if you can help me accomplish this in ASP Classic.

To make this question more relevant, I have posted this question in the ASP Classic Section as well. The link to the ASP Classic Question Page is as follows:

Please Visit This Page!


I appreciate any kind of help you might have for me. You seem to have a special method or technique of your own that is unique.

I hope I can use your sophisticated method in my ASP Source Code.

Thank you again for being so helpful and informative.

Good luck, Delerna.
Sep 23 '10 #21

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Phil Powell | last post by:
Very simplistic but I am utterly STUMPED at this one. I have a db table "person" that I can sort by title, first_name, last_name, or city with no problems at all because "title", "first_name",...
3
by: Lad | last post by:
What is the best( easiest)way how to sort a file? I have a file where each record consists of 3 fields( 3 words) and I would like to sort records by the first field( word)in each record. Any idea?...
12
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course,...
22
by: Nhmiller | last post by:
Is there a way to do this? Thanks. Neil Cat Paintings At Carol Wilson Gallery http://www.carolwilsongallery.com
3
by: srikanth | last post by:
please give me any helpful logics for the subject
1
by: rdraider | last post by:
Hi all, We have an app that uses SQL 2000. I am trying to track when a code field (selcode) is changed on an order which then causes a status field (status) to change. I tried a trigger but...
3
by: usaccess | last post by:
Hi, I have a form/subform. There is a priority field and then a case status field. I want to display records so that it sorts by case status as "actives" then "on holds" then "completes" and...
2
by: Catch_22 | last post by:
Hi, I have a stored procedure that has to extract the child records for particular parent records. The issue is that in some cases I do not want to extract all the child records only a...
3
by: sail87 | last post by:
I inherited an Access 2003 database and need to change the way a form is sorted. It seems to work like this. There is a table containing work records of work requests. The user fills out a form...
3
by: ITSimTech | last post by:
I'm trying to learn how/do two things here: 1) If the user searches for "Data" ($searchtext = "Data") the output should also include the fourth record because Field1 contains "all". 2) But the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.