Results 1 to 12 of 12
  1. #1
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85

    Sorting in Query

    I want to sort a colum that has numbers - it sorts

    6
    24
    23
    23.75

    I want it to sort
    24
    23.75
    23
    6

    How do I make it sort this way?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname]))

    I tried this - but it still is not giving me the correct order

    24K
    23K
    23.75K
    6K

    it sorts like this
    6K
    24K
    23K
    23.75K

    I need it to sort
    24K
    23.75K
    23K
    6K

    Is there something else that I need to add to the expression?
    Last edited by Rhubie; 09-21-2012 at 01:34 PM. Reason: additional info

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    SELECT Pricing.ID, Pricing.CategoryCode, Pricing.WBCode, Pricing.Classification, Pricing.KaratWeight, Pricing.Description, Pricing.PrintableDescription, Pricing.Size, Pricing.TypeOfLeaf, Pricing.GramWeight, Pricing.WBCost, Pricing.ExchangeRate, Pricing.RetailMargin, Pricing.WBShipping, Pricing.ToolsSizingEtcRetailPrice, Pricing.WholesaleMargin
    FROM Pricing
    ORDER BY Pricing.CategoryCode, Pricing.KaratWeight DESC;

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ok, can you tell me which field represents the 24K value? Why is the K part of the field if you want a numeric sort?

    Can you show the result of the query you posted? Just so I can see which fields go with what data.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I ran a small mock up of your data

    Code:
    SELECT CDbl(Mid([Sortfield],1,Len([sortfield])-1)) AS Expr1
    FROM SortTable
    ORDER BY CDbl(Mid([Sortfield],1,Len([sortfield])-1)) DESC;
    You have to drop the alpha K from the text string; then you convert the remaining text string (the numeric part) to dbl; then use that field to sort.

    Good luck with your project.
    Attached Thumbnails Attached Thumbnails SortDescendingWithoutTheK.jpg  

  8. #8
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    KaratWeight is the field for 24K value
    Click image for larger version. 

Name:	test.jpg 
Views:	6 
Size:	175.2 KB 
ID:	9241Click image for larger version. 

Name:	test.jpg 
Views:	6 
Size:	175.2 KB 
ID:	9241

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I am getting an error - Invalid Use of Null

    I put the code into a field in the query - is that not right?

    I got it I have null values in that field also.

    Thanks

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    smithse is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    37
    and just my 2 cents worth, if the "K" which represents karats or carats, is always going to be "K", then remove it as part of the field or value, and you can always append this in a report or query as it's purely a 'visual' indicator like a dollar sign for example, especially if by-rights this is a numeric field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sorting Query through Forum for range
    By dbalilti in forum Access
    Replies: 22
    Last Post: 08-14-2012, 11:13 AM
  2. Replies: 11
    Last Post: 01-12-2012, 07:55 PM
  3. Sorting a Union query
    By coach32 in forum Queries
    Replies: 1
    Last Post: 09-20-2011, 10:23 PM
  4. Sorting in a Query (Dates)
    By JohnS in forum Queries
    Replies: 2
    Last Post: 10-23-2009, 03:51 AM
  5. Is this a Query or Sorting problem?
    By bwrobel in forum Queries
    Replies: 2
    Last Post: 07-14-2006, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums