Sorting numbers in a text field

[mpfohl]

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?

 

[fredg]

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

 

[mpfohl]

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.

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

 

[raskew via AccessMonster.com]

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

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?
[quoted text clipped - 32 lines]

Please respond only to this newsgroup.
I do not reply to personal e-mail