CODEWITHSUNDEEP
ms-access
sam
sam

Reputation: 85

How to sort text column that has mostly numbers and a dot

In MS Access , I have a field by name "TargetDays" that has values like "0", "13", 20", "6", "1", "9", ".""2", "28"

I want them to be sorted as 

., 0, 1, 2, 6, 9, 13, 20, 28

I tried doing ORDER BY val(TargetDays) But this sorts sometimes as ., 0, 1, 2, 6, 13, 20, 28. But other times it sorts as 0, ., 1, 2, 6, 13, 20, 28. The problem is coming with "." and "0".

Could someone please tell me a solution to sort in the intended order (as mentioned above)?

Upvotes: 1

Views: 41

Answers (1)

HansUp
HansUp

Reputation: 97101

That happens because Val(".") and Val("0") both return 0, so your ORDER BY has no way to distinguish between those 2 characters in your [TargetDays] field ... and no way to know it should sort "." before "0".

You can include a secondary sort, based on ASCII values, to tell it what you want. An Immediate window example of the Asc() function in action ...

? Asc("."), Asc("0")
 46            48

You could base your secondary sort on that function ...

ORDER BY val(TargetDays), Asc(TargetDays)

However, I don't think you should actually need to include the function because this should give you the same result ...

ORDER BY val(TargetDays), TargetDays

Upvotes: 1

Related Questions