I have a function that currently takes the users input and builds a query to get the answer. The query is a beast of a size but gets the data fairly quickly.

I'm using MS-Access as the database so no fancy SQL tricks are available.

What I will get might be:

things

or:

things stuff "long bits and soforth" and my thingy

What I need to do is pull out the phrases marked by quotes and add them as they are to an array and remove them from the string so I can continue to do a split using the white space and add that array to the first.

I also need to remove any common words but only from the NON quoted text(another array currently holds the common words).

What I then have is a collection of search terms.

My plan is to run a loop that builds a very fat UNION query with a collection of Sum and Group etc.

So far I have a reasonable union going on but the string manipulation is not

I need a little help as I must get this to be as efficient as possible.

This is my SQL so far:
Code:
searchStrSQL = "Select * from ListLevel INNER JOIN " &_
"(" &_
"SELECT " &_
"EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, " & _
"fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone, " & _
" Sum(Rel) as SumOfRel FROM (" & _
"SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%" & StrSearch & "%'" & _
" Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%" & StrSearch & "%'" & _
" Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%" & StrSearch & "%'" & _ 
" Union SELECT *, 4 as rel FROM [Q_Pages] where Region.Region like '%" & StrSearch & "%'" & _
" ) T1 Group By " & _
"EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, " & _
"fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone " & _
" Order by Sum(rel)" &_
") T2 " &_
"ON T2.T1.ListLevel = ListLevel.ListLevelID;"
Else
searchStrSQL = "Select * from ListLevel INNER JOIN " &_
"(" &_
"SELECT top 1 * " & _
"from [Q_pages] where [Entity].[EntityID] = " & StrSearch  &_
") T2 " &_
"ON T2.ListLevel = ListLevel.ListLevelID;"
now what's the best way to chop up the search string and build said SQL with neasted AND / OR conditions.