Complex Queries in ADO.NET, what is and isn't supported from Access?
Posted on 2005-04-01
I have a query that works fine in Access, and I have experianced in the past that Access supports alot more features in it's queries in the way of running functions and structure wise.
However I really need to port this little bugger over to ADO.NET for an ASP.NET and VB.NET application.
Basically I put into a Temporary table search results for clients then have this query to process the results.
It only tells me it can't parse it, no other help on what is causing an error.
SELECT ClientSearch.ClientID, (SUM(lettercount([Word])) / lettercount([Company])) * (SUM(lettercount([word])) / lettercount([SearchedFor])) AS Rating,
tblClientData.Company, tblClientData.FirstName AS [First Name], tblClientData.Surname, tblClientData.Street AS Address1,
tblClientData.Street2 AS Address2, tblClientData.Suburb, tblClientData.State, tblClientData.PostCode, COUNT(ClientSearch.Word) AS CountOfWord,
Format([Rating], '0.0%') AS Percentage, ClientSearch.SearchedFor, lettercount([SearchedFor]) AS LtrSearchedFor, lettercount([Company]) AS LtrCompany,
SUM(lettercount([Word])) AS LtrWord
FROM tblClientData RIGHT JOIN
ClientSearch ON tblClientData.CLID = ClientSearch.ClientID
GROUP BY ClientSearch.ClientID, tblClientData.Company, tblClientData.FirstName, tblClientData.Surname, tblClientData.Street, tblClientData.Street2,
tblClientData.Suburb, tblClientData.State, tblClientData.PostCode, ClientSearch.SearchedFor
ORDER BY (SUM(lettercount([Word])) / lettercount([Company])) * (SUM(lettercount([word])) / lettercount([SearchedFor])) DESC;
THESE ARE THE FUNCTION I HAVE IN THE GENERAL MODULE
Public Function WordCount(strWord As String) As String
Dim arrWords() As String
Dim theWord As String
arrWords = Split(Trim(strWord), " ") ' split it by " " so we have a 0 based count of words
WordCount = UBound(arrWords) + 1 ' return the count of the array + 1
Public Function LetterCount(strWord As String) As String
Dim strJoinedWord As String
strJoinedWord = Replace(Trim(strWord), " ", "") ' if string contains spaces (we don't want to count em)
LetterCount = Len(strJoinedWord) ' return the len of the string