 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / July 2005How do I sort numbers & text in an Access text field
I have data in a field e.g. 1 3 0 5 24 36789 L345 MS234 etc.... What I need is some way of sorting the so the numbers come first and in numeric order then the letter number combinations. Hi, you can first order by Val([MyField]) and then by [MyField]  SignatureAlex Dybenko (MVP) http://Alex.Dybenko.com http://www.PointLtd.com >I have data in a field e.g. > 1 [quoted text clipped - 8 lines] > What I need is some way of sorting the so the numbers come first and in > numeric order then the letter number combinations. I might be being thick here but this does not appear to sort them as expected. The Val part does put the numbers in the correct order but the alpha numerics get a value of 0 so come first in the query. If I then try to do the second order you are back to square one with 1,10,100 etc as the numeric sort, and the alpha numerics are not in order eg B80 comes before B8 > Hi, > you can first order by Val([MyField]) and then by [MyField] [quoted text clipped - 11 lines] > > What I need is some way of sorting the so the numbers come first and in > > numeric order then the letter number combinations. Try ORDER BY IsNumeric(MyField) Desc, Val(MyField), MyField IsNumeric should return -1 (true) and 0 False. Val will return the value and will still return zero for the non-numbers, but they are forced to the second section by the order by criteria. > I might be being thick here but this does not appear to sort them as expected. > The Val part does put the numbers in the correct order but the alpha [quoted text clipped - 22 lines] > > > What I need is some way of sorting the so the numbers come first and in > > > numeric order then the letter number combinations. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.