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)?
Sam AAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
So, these values are stored as text, not numbers?

you might try a computed column that looks something like:

SortOrder: IIF([TargetDays] = ".", -1, Val([TargetDays]))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
The ONLY way to sort a string in proper numeric order is to right justify the string.  You can do this by filling the string with leading zeros or leading spaces.  To do it with spaces (less visually jarring to users if you want to display as right justified but you don't need to.  I don't.  I use the rightJust field only for sorting):

Select Space(15 - Len(YourField) & YourField as RightJustYourField, YourField, .....  From YourTable
Order By Space(15 - Len(YourField) & YourField

I picked 15 as the field length.  Use what ever makes sense as a max.  Keep in mind that if your field has delimiters such as 1.10.23, then you need to right justify each stanza.
0
Gustav BrockCIOCommented:
If you sort on the dot first, then the value:

ORDER BY [TargetDays], Val([TargetDays])

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.