Re: 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

mpfohl@xxxxxxxxxxx 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/Forums.aspx/access-queries/200702/1

.

Relevant Pages

  • Re: How to achieve scalability
    ... You could create a column in a query like the following. ... Dim strHold As String ...
    (comp.databases.ms-access)
  • Re: Function needed - String Part(Source,Section)
    ... StringPart(cFoo,2) should return "ab". ... Dim strToCheck As String ... Dim intCnt As Integer ... Dim strHold As String ...
    (comp.databases.ms-access)
  • Re: How to achieve scalability
    ... I'd suggest, when testing, to create a query that selects 3 or 4 names from the table so it doesn't calc the entire lot in case there's a code error. ... Dim strHold As String ...
    (comp.databases.ms-access)
  • Re: HELP Appreciated
    ... Function fFixString(pstr As String) As String ... Dim strHold As String ... Dim strDump As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Counting specific chars in a string
    ... Function StrCount(ByVal TheStr As String, ... Dim strHold As String ... Dim itemhold As Variant ...
    (comp.databases.ms-access)
Loading