MS Access SQL Word Frequency

kinneh

kinneh

kinneh

Associate
Joined
18 Oct 2002
Posts
724
Hi

I'm trying to build a search system using MS ACCESS (and classic asp) which returns the most relevant results at the top.

I have a memo field that stores a large description. Is it possible to calculate how many times a string appears in this description?

E.g. If the word 'advisor' appears in the description 10 times, I need it to return the number 10.

Thanks for your help.
 
Not used Access for years but the following would work in SQL Server so should be fine in access

DECLARE @dataToSearchThrough VARCHAR(2000)
DECLARE @strToSearchFor VARCHAR(2000)
SET @dataToSearchThrough = 'I like to harvest harvest, I like to harvest'
SET @strToSearchFor = 'harvest'



SELECT (LEN(@dataToSearchThrough) - LEN(REPLACE(@dataToSearchThrough, @strToSearchFor, '')))/LEN(@strToSearchFor) AS NumTimesWordAppears
 
This works fine when executing the query in MS Access.

However, when i try and execute the the stored query using my classic ASP script, it errors.

Code: 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

Basically, JET doesn't support the Replace function.

Do you know if its possible to get this working at all?

My code to execute the query is below:

Code: 
query = "EXECUTE q_storedQuery '" & parameterVariable & "'"
set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open query, DataConnVac, adOpenKeyset, adLockOptimistic
 
Not sure perhaps you could use ODBC. I don't use access. In SQL Server you can create stored procedures so perhaps that's an option.
Might be worth searching on stackoverflow and google to see if anyone else has had this problem.
 
You must log in or register to reply here.
Back
Top Bottom