Sorting in Query

[Rhubie]

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?

[Bob Fitz]

Perhaps this will help: http://office.microsoft.com/en-us/ac...010222312.aspx

[Rhubie]

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?

[orange]

Please post the SQL of your query

[Rhubie]

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;

[orange]

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.

[orange]

I ran a small mock up of your data

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.

[Rhubie]

KaratWeight is the field for 24K value

[orange]

Please see my last post that shows how to achieve the sort order you want.

[Rhubie]

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

[orange]

Add a WHERE clause, WHERE yourfield Is Not NULL

[smithse]

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.