Calling a function within a query

• • Reply

  15 years ago

  by ill_comms

  Hayden Fisher

  New Zealand, New Zealand

  Joined 17 years ago

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

  Report abuse