How do I sort a query when one field is based on a UDF?

[tvsmvp]

How do I get my query (which creates the row source for a list box) to sort correctly when the column I'm sorting on is a UDF? Apparently it's sorting before doing the calculations - not after. Rerunning the query in the list box's after change event simply reruns the query - which of course brings the same results.

 

[PHV]

Any chance you could post the SQL code of this query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tvsmvp]

No problem -

Here's the SQL:
SELECT Articles.ID, Articles.PageTitle, GetWordCount('horse',1,[Article]) AS CountArt2
FROM Articles
ORDER BY GetWordCount('horse',1,[Article]) DESC;

Here's the UDF:
Function GetWordCount(strMyString, n, myField)
On Error Resume Next
StrippedWord = Split(strMyString, " ")(n - 1)
If StrippedWord <> "" Then
GetWordCount = (Len(myField) - Len(Replace(myField, StrippedWord, "", 1, -1, 1))) / Len(StrippedWord)
Else
GetWordCount = 0
End If
End Function

Funny how it's the little things I spend the most time on...

 

[PHV]

And what about this ?
SELECT ID, PageTitle, GetWordCount('horse',1,[Article]) AS CountArt2 FROM Articles ORDER BY 3 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tvsmvp]

Nope - still does it. I have noticed that what it's doing is sorting by the first digit:
8, 7, 4, 28, 27, 23, 2... etc.

There's gotta be a fix for that just staring me in the face; I just don't see it.

 

[PHV]

Perhaps this ?
Function GetWordCount(strMyString, n, myField) [!]AS Integer[/!]

Or back to the orig:
SELECT Articles.ID, Articles.PageTitle, GetWordCount('horse',1,[Article]) AS CountArt2
FROM Articles
ORDER BY [!]Val([/!]GetWordCount('horse',1,[Article])[!])[/!] DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tvsmvp]

Yes! Genius! The addition of "as integer" worked like a charm. Thanks!