Calling a function from a query

[Hayden25]

Hi all,

Description:Firstly I'm creating a search page with ASP, the user can enter up ten words in an input.

When submitted the an entered string is split into an array, one keyword per array element.

A loop will be created calling a stored query in MS Access in turn for each keyword. The stored query will have one parameter that will be @KEYWORD as below. The memo field is called CONTENT and to start with I'm just getting the CONTENT_ID back from the query.

SELECT PARAGRAPH_ID
FROM PARAGRAPHS
WHERE CONTENT LIKE '* @KEYWORD *';

this works fine, but I would like to make it do more.

Question:
What I would like it to do is, for each record found be able to count how many times that keyword is in the CONTENT field. Am I able to do this within a user-defined function(not sure if MS Access has them or something similar) or the stored query itself?

For the user-defined function
What I would do is create it, it would have the parameters @CONTENT and @KEYWORD. Iterate through it producing a count of keyword occurances, which in turn would be returned to the stored query. Thus this will give me a way to effectively search through my content and be able to rank the results.

What I'll need help with is how to call a function from a query and also how to create the function.

Example:
SELECT PARAGRAPH_ID, (CNT_KEY[CONTENT,@SEARCH_KEYWORD]) AS KEYWORD_COUNT
FROM PARAGRAPHS
WHERE CONTENT LIKE '* ' + @SEARCH_KEYWORD + ' *';

and an example module function would be

Function CNT_KEY(CONTENT As String, KEYWORD As String)

If (Len(KEYWORD) < Len(CONTENT)) Then
KEYWORD_COUNT = 0
KEYWORD_POS = 1
START_POS = 0
While (KEYWORD_POS > 0)
KEYWORD_POS = InStr(START_POS, CONTENT, KEYWORD)
If (OCCURANCE > 0) Then
KEYWORD_COUNT = KEYWORD_COUNT + 1
START_POS = KEYWORD_POS + (Len(KEYWORD) - 1)
End If
Wend
COUNT_KEYWORD = KEYWORD_COUNT
Else
COUNT_KEYWORD = 0
End If
End Function

Any Ideas Out There???

 

[SeeThru]

You should be able to do it....

A simple way would be to pass the whole text of the paragraph, plus the keyword to a function..ie...

Function KeywordCount(Paragraph, Keyword)
dim tmpString as variant, keywordcount as integer
intkeywordcount=0
tmpstring=paragraph
do while instr(tmpstring, keyword)>0 then
intkeywordcount=intkeywordcount+1
tmpstring=mid(tmpstring, instr(tmpstring, keyword)+len(keyword)
loop
keywordcount=intkeywordcount


You could also just pass the paragraphID, look up the text and do something similar.

 

[Hayden25]

Yeap your function has the right idea so we're definitely on the same wavelength, I guess the next question would be where would I define this function and how to call it from the query as I am totally stumped on this bit.