ADVANCED ORDERING

[Silvinho]

I’m having a problem which I’m not sure is even achievable.
I’ve created a search results page that works normally i.e.:

strSearch = Request.Form(“search”)
SELECT * FROM Table1 WHERE Field1 LIKE ‘%strSearch%’

Say the form variable is ‘Dog’
What I want to achieve is to count the number of times the variable ‘Dog’ appears in the search.

The ultimate aim was to order the query by the number of times the variable appeared so this might not be the best way to do this but I cant think of anything better for now.

Anyway any help would very helpful.

 

[AskMan]

Hi,

I think you should add some steps
- Create a temporary table with the records you obtain with your query.
- Calculate the number of times your variable appears for each record (see this function) ....

***********************************************************
Function NbTimes(txtSearched, Text) As Long
Dim i As Long
NbTimes = 0

If Len(Text) < Len(txtSearched) Then
MsgBox &quot;the string searched is longer than the text !&quot;
Exit Function
End If

i = 1
While Not InStr(i, Text, txtSearched) = 0
NbTimes = NbTimes + 1
i = InStr(i, Text, txtSearched) + 1
Wend

End Function
***********************************************************

- then, in the temporary table, you can update a field &quot;NbTimes&quot;
- And finally, your query for your results will be based on the temporary table and oredred by &quot;NbTimes&quot;

I'm sorry if it's not if it's not easy to understand but my english is not really fluent

Hope that can help
Seb

 

[JeremyNYC]

SELECT count(field2) as Hits FROM Table1 WHERE Field1 LIKE ‘%strSearch%’

will return the number of records with dog somewhere in field1. Is that what you're looking for?

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.