Why would 010322-01 output before 092521-01 in a query when the field is sorted ascending?
Why would 010322-01 output before 092521-01 in a query when the field is sorted ascending?
Because 01 comes before 09. Why would you expect otherwise?
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.
Go on, I'll bite.
Why would you think otherwise?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
The values are obviously text, so once that question is figured out, it might come as a surprise to OP that
111
comes before
22
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
It is a "short text" field currently, but would it be possible to alter a parameter to cause it to act as if this were a number with 010322-01 being larger than 092521-01?
you still think that 10322 is larger than 92521? Why? Ten thousand + some hundreds will never be larger than 92 thousand + some hundreds??act as if this were a number with 010322-01 being larger than 092521-01?
IMO, your only probable solution to get what you want is to either
- add a sort field & assign real numbers to these values in the order you want. I'd increment by 5's or 10's not 1's OR
- split the parts into real numbers and concatenate the parts in your forms/reports/queries
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
No.would it be possible to alter a parameter to cause it to act as if this were a number with 010322-01 being larger than 092521-01?
Depending on your application, you might consider a "Sequence" field to allow sorting.
Found a workaround. Will sort on a "date" field before sorting on the "work-order number" field in the queries. This seems to provide the required results. Thanks to everyone. Yes, I have a lot to learn.
Always better for communication and more focused responses when the issue is presented in context. Happy to hear there was a "date" field available for sorting.