I have a text field that contains data such as 1st, 2nd, 3rd etc. When I try to sort I am getting sorts such as 1st, 20th, 29th, 2nd. How can I correct the issue?
Thanks
Jim O
I have a text field that contains data such as 1st, 2nd, 3rd etc. When I try to sort I am getting sorts such as 1st, 20th, 29th, 2nd. How can I correct the issue?
Thanks
Jim O
The field is text type so it is sorting by alpha hierarchy.
Construct a field that extracts the number part and sort on that field.
Val([fieldname]) AS SortNum
Otherwise, create a number type field and don't use ordinals.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks for the input. Got it working now.
Jim O