If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting numbers in a text field
I have a text field I want to sort.
I have this: 1 10 11 2 3 FM 1 FM 11 FM 3 I would like the numbers to be in order. Is there a way to tell it to sort the text as text but sort the numbers as numbers? |
#2
|
|||
|
|||
Sorting numbers in a text field
|
#3
|
|||
|
|||
Sorting numbers in a text field
It's close but still not quite right. The numbers do fine, but i'd
like the end to look like: FM 1 FM 3 FM 11 and not, as your way produced. FM 1 Fm 11 FM 3 But it's very close, thank you. Any suggestions on how to get to the last step? On Feb 26, 4:56 pm, fredg wrote: On 26 Feb 2007 14:29:06 -0800, wrote: I have a text field I want to sort. I have this: 1 10 11 2 3 FM 1 FM 11 FM 3 I would like the numbers to be in order. Is there a way to tell it to sort the text as text but sort the numbers as numbers? SELECT tblBasicData.CombinedText FROM tblBasicData WHERE (((tblBasicData.CombinedText) Is Not Null)) ORDER BY IIf(Val([CombinedText])=0,[CombinedText]), IIf(Val([CombinedText])0,Val([CombinedText])); Text Field Sorted Result 1 2 3 10 11 FM 1 FM 11 FM 3 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
Sorting numbers in a text field
Fred -
Neat solution! I'm still puzzling over how the nested Iif works. It looks like it's missing a False portion. Anyway, it works great. Function Savenumer(), below, removes non-numeric characters from a string. Utilizing this added to your SQL appears to sort it as desired. SELECT tblBasicData.CombinedText FROM tblBasicData WHERE ((Not (tblBasicData.CombinedText) Is Null)) ORDER BY IIf(Val([CombinedText])=0,Val(savenumer([CombinedText]))) , IIf(Val([CombinedText])0,Val([CombinedText])); ************************************************** ********************* Function SaveNumer(ByVal pstr As String) As String '******************************************* 'Purpose: Removes alpha characters from a string 'Coded by: raskew 'Calls: Function IsNumeric() 'Inputs: ? SaveNumer(" t#he *qu^i5ck !b@r#o$w&n 4fo#x ") 'Output: 5 'Note: As written, empty spaces are ignored. '******************************************* Dim strHold As String Dim intLen As Integer Dim n As Integer strHold = Trim(pstr) intLen = Len(strHold) n = 1 Do If Mid(strHold, n, 1) " " And Not IsNumeric(Mid(strHold, n, 1)) Then strHold = Left(strHold, n - 1) + Mid(strHold, n + 1) n = n - 1 End If n = n + 1 Loop Until Mid(strHold, n, 1) = "" SaveNumer = strHold End Function ************************************************** ********************* Bob wrote: It's close but still not quite right. The numbers do fine, but i'd like the end to look like: FM 1 FM 3 FM 11 and not, as your way produced. FM 1 Fm 11 FM 3 But it's very close, thank you. Any suggestions on how to get to the last step? I have a text field I want to sort. [quoted text clipped - 32 lines] Please respond only to this newsgroup. I do not reply to personal e-mail -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200702/1 |
Thread Tools | |
Display Modes | |
|
|