Keyword Density Search

[daddio2005]

I have a table that contains a memo field which has lots of information in it. I wish to search through each row in that table and return of count of how many times a word occurs for each row.

I know the COUNT() function counts the number of rows but I need to count the number of occurances in each row.

Any suggestions would be appreciated
thanks.

 

[dhookom]

This function should count all occurances

Public Function CountOccurances(pvarText As Variant, _
        pvarFind As Variant) As Integer
    Dim lngLastFind As Long
    Dim intLen As Integer
    
    If IsNull(pvarText + pvarFind) Then
        CountOccurances = 0
     Else
        intLen = Len(pvarFind)
        intLastFind = InStr(pvarText, pvarFind)
        Do Until intLastFind = 0
            CountOccurances = CountOccurances + 1
            intLastFind = InStr(intLastFind + intLen, _
                pvarText, pvarFind)
        Loop
    End If
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daddio2005]

Thanks for your reply.

Ideally I was looking for a SQL statement that would do this query for me, is such a thing possible?

Also not sure how to integrate the above function into results from a recordset?

Cheers.

 

[dhookom]

I doubt this can be accomplished in pure sql. There might be some regular expressions syntax that would do this but I'm not up on RE.

You haven't told us how you want to "integrate" the results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daddio2005]

Basically the table contains page names and page content. Upon deciding on a keyword to search for, I want to return all page names that have that word in the content and ORDER IT BY DENSITY/RELEVANCE.

Any ideas would be appreciated.

thanks

 

[dhookom]

Try:

SELECT PageName, CountOccurances(PageContent,"Howdy") as HowMany
FROM [table without a name given]
ORDER BY CountOccurances(PageContent,"Howdy") DESC

You should be able to replace the Howdy with a reference to a control on a form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daddio2005]

How does this work in an ASP page? Surely the function is written in the ASP page but the SQL query is run on the database.
How will this mix correctly?

Thanks.

 

[dhookom]

I missed any mention of ASP in your earlier postings. A user-defined function is Access is not exposed to your ASP page. You will need to write the function in your VBScript or other.

You should attempt to be a little more clear about your environment in future posts.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]