Multiple word SQL loop search from words entered

You are going to have to break up each keyword entered on you textboxes into individual ones, and build your SQL query dynamically, adding an extra AND LIKE block. See below...
You just have to figure out how you want to build your query dynamically based on the number of keywords.

RecordSet1Source="SELECT * FROM ORDERS WHERE (onumber LIKE '%"keyword1"%' "
--Following is just pseudo code
KEYWORD_COUNT = NUMBER OF KEYWORDS;
WHILE KEYWORD_COUNT > 0
BEGIN
    RecordSet1Source = RecordSet1Source +"AND onumber LIKE '%"keyword(keyword_count)"%'"
    KEYWORD_COUNT = KEYWORD_COUNT-1
END
RecordSet1Source = RecorSet1Source + ")

Select allOpen in new window

By the way, I oversimplified your actual query.

I wrote some code a while ago that I use to filter the current recordset on a continuous form.  Basically, this code allows my to type a series of key words, with boolean (+/-) assignments and parenthesis so that I can create a complex criteria and filter a recordset based on it.  On my form, I have a control (txt_SearchFor) in which I type the values I do (or don't) want to see.  When I click the Filter button, the code creates a criteria string that I place in the Filter property of my form.  I also display the actual criteria string in a textbox (txt_Filter), just so I can make sure that the criteria says what I really want it to.

In your case, you could use this filter (varFilter) variable to concatenate to your SQL string as the WHERE clause.  Since you are using SQL Server, you will have to change my wildcards '*' to '%', but this should give you a starting point to work from.

Private Sub cmd_Filter_Click()

    Dim varFilter As Variant
    Dim strLeadParen As Variant, strTrailParen As Variant
    Dim aFilter() As String, strHow As String
    Dim intLoop As Integer
    Dim strSQL As String
    
    varFilter = Null
    aFilter = Split(Me.txt_SearchFor, " ")
    For intLoop = LBound(aFilter) To UBound(aFilter)
        If Len(Trim(aFilter(intLoop) & "")) > 0 Then
            
            If Left(aFilter(intLoop), 1) = "(" Then
                strLeadParen = "("
                aFilter(intLoop) = Mid(aFilter(intLoop), 2)
            Else
                strLeadParen = ""
            End If
            
            If Right(aFilter(intLoop), 1) = ")" Then
                strTrailParen = ")"
                aFilter(intLoop) = Left(aFilter(intLoop), Len(aFilter(intLoop)) - 1)
            Else
                strTrailParen = ""
            End If
            
            If Left(aFilter(intLoop), 1) = "+" Then
                strHow = " AND "
                aFilter(intLoop) = Mid(aFilter(intLoop), 2)
            ElseIf Left(aFilter(intLoop), 1) = "-" Then
                strHow = " AND NOT "
                aFilter(intLoop) = Mid(aFilter(intLoop), 2)
            Else
                strHow = " OR "
            End If
            
            varFilter = (varFilter & strLeadParen & IIf(IsNull(varFilter), "", strHow)) _
                      & "([Field_N] LIKE ""*" & aFilter(intLoop) & "*"")" & strTrailParen
        
        End If
    Next intLoop

    'Fills in a textbox on the form which displays the actual filter string (for debugging purposes)
    Me.txt_Filter = varFilter
    If Len(varFilter & "") > 0 Then
        Me.Filter = varFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub

Select allOpen in new window

In attempting to use myrotarycar's code, I receive the following error:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/administrativeorders/results.asp, line 23

RecordSet1Source="SELECT * FROM ORDERS WHERE (body LIKE '%"keywords"%' "
-----------------------------------------------------------^

Question, what would the new SQL statement look like?  I need this new function to search the ONUMBER and BODY fields.  The statement given only searches the ONUMBER field.  If I could see the entire statement with explanations, that would be greatly appreciated.  Thank you.

I was thinking of something like this:

SELECT * FROM ORDERS 
WHERE 
	(
		onumber LIKE '%"keyword1"%' 
		OR body LIKE '%"keyword1"%' 
		OR onumber LIKE '%"keyword2"%' 
		OR body LIKE '%"keyword2"%' 		
		OR onumber LIKE '%"keyword3"%' 
		OR body LIKE '%"keyword3"%' 
		OR onumber LIKE '%"keyword4"%' 
		OR body LIKE '%"keyword4"%'
	)
AND
	subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"#"

Select allOpen in new window

Also, take a close look at fyed's proc. Looks as if he's done most of the leg work on the VB side.

Being the novice I am, obviously I'm missing a few things.  This is what I have (see below).  I am still getting errors.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

Recordset1.Source="SELECT * FROM ORDERS WHERE (body LIKE '%"keywords"%' " 
'Following is just pseudo code 
KEYWORD_COUNT = NUMBER OF KEYWORDS; 
WHILE KEYWORD_COUNT > 0 
BEGIN 
    Recordset1.Source = RecordSet1Source +"AND body LIKE '%"keyword(keyword_count)"%'" 
    KEYWORD_COUNT = KEYWORD_COUNT-1 
END 
Recordset1.Source = Recordset1.Source + ") "

Recordset1.Source="SELECT * FROM ORDERS  
WHERE  
        ( 
                onumber LIKE '%"keyword1"%'  
                OR body LIKE '%"keyword1"%'  
                OR onumber LIKE '%"keyword2"%'  
                OR body LIKE '%"keyword2"%'              
                OR onumber LIKE '%"keyword3"%'  
                OR body LIKE '%"keyword3"%'  
                OR onumber LIKE '%"keyword4"%'  
                OR body LIKE '%"keyword4"%' 
        ) 
AND 
        subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"#" 

Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open() 
Recordset1_numRows = 0 
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>

Select allOpen in new window

Welcome to the club. OK, first your immediate problem: You copy/pasted my first sample which is purely pseudo-code. Only intended to be used as your guide to what you can probably code around.
Looks as if you need help on the VBA side; I would hit the request attention button up top and ask that they try to involve some of the really big-guns in VB for Applications / Macro coding. They will certainly be able to help you translate the pseudo-code into actual VB you can plug in or work with.

The syntax error on:
Recordset1.Source="SELECT * FROM ORDERS WHERE (body LIKE '%"keywords"%' " 

is because there is no concatenation:

Recordset1.Source="SELECT * FROM ORDERS WHERE (body LIKE '%" & keywords & "%' " 

The Where clause needs to be created dynamically in a loop similar to what fyed has posted. But you need verify your SQL before passing it to the database. As a test pop up the string to look at it before sending it to the database. What does the SQL string which generates the error looks like?

Leon

When I attempt a query on keywords entered, I get the following error message:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/administrativeorders/results.asp, line 18

KEYWORD_COUNT = NUMBER OF KEYWORDS;
-----------------------^


Is it possible to display the entire SQl statement that will give me the results I am searching for?  Thank you.

I am using this combination of suggested solutions:

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\administrativeorders\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

Recordset1.Source="SELECT * FROM ORDERS WHERE (body LIKE '%" & keywords & "%' " 
'Following is just pseudo code 
KEYWORD_COUNT = NUMBER OF KEYWORDS; 
WHILE KEYWORD_COUNT > 0 
BEGIN 
    Recordset1.Source = RecordSet1Source +"AND body LIKE '%"keyword(keyword_count)"%'" 
    KEYWORD_COUNT = KEYWORD_COUNT-1 
END 
Recordset1.Source = Recordset1.Source + ") "

Recordset1.Source="SELECT * FROM ORDERS  
WHERE  
        ( 
                onumber LIKE '%"keyword1"%'  
                OR body LIKE '%"keyword1"%'  
                OR onumber LIKE '%"keyword2"%'  
                OR body LIKE '%"keyword2"%'              
                OR onumber LIKE '%"keyword3"%'  
                OR body LIKE '%"keyword3"%'  
                OR onumber LIKE '%"keyword4"%'  
                OR body LIKE '%"keyword4"%' 
        ) 
AND 
        subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"#" 

Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open() 
Recordset1_numRows = 0 
%>

Select allOpen in new window

Basically what bhess1, but instead of Recordset1.Source = "Select ....

I would say use;

Dim strSQL
strSQL = "Select ...
msgbox strSQL

Leon

I attempted the following code and received the following error:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/administrativeorders/results.asp, line 36

Next I
-----^

What am I doing wrong?

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\administrativeorders\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

		
Dim keywords

kw=Split(keywords, ".")

Dim sep
Dim sWhere

sep = "("
Dim I
For I = LBound(kw) to UBound(kw)
    sWhere = sWhere & Sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
Next I
sWhere = sWhere & ")"


Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere
msgbox strSQL


Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open() 
Recordset1_numRows = 0 
%>

Select allOpen in new window

Change:

Recordset1.Open()

To

Recordset1.Open

And let us see the output of the msgbox please.

Leon

I went ahead and modified.  I still get the following error message:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/administrativeorders/results.asp, line 29

Next I
-----^

asp.net - I forgot.  That should just be

next

The "I" is extraneous.

Permission was denied.  So I went ahead and used:

response.write strSQL
response.end

This is what I get (below).  It did not pick up the two words I typed into the KEYWORDS text box.

SELECT * FROM Orders WHERE subdate BETWEEN #10/16/1979# AND #3/8/2010# AND )

Change:

Dim keywords

kw=Split(keywords, ".")


To

kw=Split(%keywords%, ".")

We need to get your field data in there.  We should also check to ensure that there is data before building the string, but let's see if this works first.

Check out my response at this site:
 (https://www.experts-exchange.com/questions/25333757/access-2003-search-multiple-values-from-same-field-from-a-form.html?anchorAnswerId=27603603#a27603603)

It includes a sample database with the code I use to do the multiple word text searches.  Since you are using SQL instead of Access, you will need to change the asterisks "*" to "%" for your wild cards, but otherwise, you could probably copy and paste the code into your app with very little change.

Also, since you are working with two fields, you could concatenate those fields together so that instead of refering to field [RandomText] you would replace that with:

[oNumber] & [Body]

I'm not sure whether the & is the right operator to use to concatenate two strings in SQL Server, but that should do it.

HTH
Dale

When I attempt BHESS1's suggestion, I receive the following error:

Microsoft VBScript compilation  error '800a0408'

Invalid character

/administrativeorders/results.asp, line 20

kw=Split(%keywords%, ".")
---------^

I noticed that when I run the SQL, I get the following:

SELECT * FROM Orders WHERE subdate BETWEEN #10/16/1979# AND #3/8/2010# AND )

You can see the AND )

In the statement, the very end appears as  & sWhere

This produces the )

If you look at the code below, line 11, sWhere = sWhere & ")"

It is not pulling:

sWhere = sWhere & Sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "

Any suggestions?

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & Sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
Next 
sWhere = sWhere & ")"


Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

Select allOpen in new window

Sep <> sep in your code

Leon

Please add a line that places the data from your entry field into a variable.  With that, we should be able to do what needs to be done.

When I applied leonstryker's suggestion, I get the following error:

Microsoft VBScript compilation  error '800a03ea'

Syntax error

sep <> "("
----^

bhess1, what do you mean by placing data into a variable?  What would the code look like?  So far, I'm only getting the dates selected in the response.write

SELECT * FROM Orders WHERE subdate BETWEEN #10/16/1979# AND #3/9/2010# AND )

Error I am now getting following leonstryker's recommendation (I believe I am entering <> into the correct location):

Microsoft VBScript compilation  error '800a03ea'

Syntax error

sep <> " OR "
----^

Below is what the code now looks like. My apologizes for frustrations if I am missing something. Thank you.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 
	
Dim keywords

kw=Split(keywords, ".")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & Sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep <> " OR "
	
Next 
sWhere = sWhere & ")"


Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

response.write strSQL
response.end


Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open 
Recordset1_numRows = 0 
%>

Select allOpen in new window

Please try the following code:

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 
	
Dim keywords

kw=Split(keywords, ".")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
	
Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

response.write strSQL
response.end

Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open 
Recordset1_numRows = 0 
%>

Select allOpen in new window

Inserted the recommended code above and received the same results:

SELECT * FROM Orders WHERE subdate BETWEEN #10/16/1979# AND #3/9/2010# AND )

What are the values of LBound(kw) and UBound(kw)?

Leon

Don't know. Not getting any results for keywords entered.

Try this

response.write keywords
response.write LBound(kw)
response.write UBound(kw)
response.end

Before the "Dim i" statement. We need to see if the items are being parsed correctly.

Leon

I typed in two words and this is the response I received:

0-1

And what are the values of

response.write keywords
response.write kw(0)
response.write kw(1)
response.end

Leon

I receive the following:

Microsoft VBScript runtime  error '800a0009'

Subscript out of range: '[number: 0]'

Lets start with simpler stuff:

response.write keywords

Waht is the value of that?

The page returns blank.  Is it because the KEYWORDS are not being requested on the search page?  Below is the updated code with the recommended response.write.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\administrativeorders\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 
	
Dim keywords

kw=Split(keywords, ".")

Dim sep
Dim sWhere


response.write keywords
response.end


sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
	
Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

response.write strSQL
response.end

Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open 
Recordset1_numRows = 0 
%>

Select allOpen in new window

Well, yeah. We are leaving it up to you to take data from the form and pass it to the code.

Please see my original code snippet at the top.  The current code is through the recommendations of the experts.  I am following advice given to me.

>I am following advice given to me.

Advise does not mean copy/paste code blindly. Please look at your code and make sure the varables match and are populated.

Leon

Obviously the variables are not being passed.  I have a feeling my request is impossible to complete.  I thought many more people would have my original problem.

If I should look at a different direction, please advise and I will close this question.  Thank you.

Its not impossible. How are the multiple words entered? We have been assuming that there is an area on your page where a user enters a number of word separated by some value (deliminator). Is this not the case?

Leon


 

Keywords are being entered into a textfield on the search page. The name of the textfield is keywords. The keywords entered seach the BODY field of the database. Nothing seperates the words entered.

>Nothing seperates the words entered

There has to be something even if its a blank space.

No seperation. I did not know this. What is your recommendation?

leon,

You're a saint for sticking with this.

Some how the following line got dropped from your code, and no one picked up on it.

Request.Form("keywords")

Instead, you are referring to a variable (keywords) that is declared, but never defined.

Try this and lets see what the SQL string reads.  It that reads OK, then get rid of the line that reads:

Response.end

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

'This is the line that has changed'	
kw=Split(Request.Form("keywords"), ".")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
	
Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

response.write strSQL
response.end

'Also added this line
Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open 
Recordset1_numRows = 0 
%>

Select allOpen in new window

There is absolutely no reason for this question to be deleted.

Leon

fyed,

Must be my penance for prior sins. :)

>No seperation. I did not know this. What is your recommendation?

No one writes words without seperation. How do you take user input?

Leon

FYED, here are the results:

SELECT * FROM Orders WHERE subdate BETWEEN #10/16/1979# AND #3/10/2010# AND (onumber LIKE '%committee shall be members%' OR body LIKE '%committee shall be members%')

I uncommented the response.write and response.end and ran the search again.  The results returned one record.  The reason one record returned was because the words "committee shall be members" are in sequential order in the record.  However, if I add another word, "department" to the search, the results page displays no records found.  Thus, I am back to square one.

ok I think this is the deal.. If the user enters comitee shall be members department, you want to return ALL the records that have comitee or shall or be or members or department in the body field.
If this is the case, then the SQL generated by your app should be
select * from Orders where subdate between #10/168/1979# and #3/20/2010# and
(
OnNumber like '%comitee%' or OnNumber like '%shall%' or OnNumber like '%be%' or OnNumber like '%members%
)
AND
(
OnBody like '%comitee%' or OnBody like '%shall%' or OnBody like '%be%' or OnBody like '%members%'
)
If this is the business requirement, then you will have to read the values from your form fields into string variables and split then into an array (putting each word into an array element)..
I can walk you through through that if this is what you are looking for.

Yes jgarzon, this is exactly what I am looking for.  Thank you!

Do you need further assistance on how to generate the dynamic SQL?

Yes, please.  Any help in finding a solution is greatly appreciated.

After applying the suggestion, I receive this message (points to new code just added):

Microsoft VBScript runtime  error '800a0009'

Subscript out of range

What is the latest version of the code? I am lost.. please post

Updated version of my code is below.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

Dim kw()
kw()=Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
	
Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM Orders WHERE subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

response.write strSQL
response.end

'Also added this line
Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()
Recordset1_numRows = 0 
%>

Select allOpen in new window

Code attached is the final working product. Many thanks to the experts who assisted.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\admin\db\admin.mdb"

dim mlocx 
mlocx="div = '"&Request.Form("div")&"' AND " 
 
if (Request.Form("div")="ALL") Then 
        mlocx="" 
end if 

Dim kw
kw=Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "onumber LIKE '%" & kw(i) & "%' OR body LIKE '%" & kw(i) & "%'"
    sep = " OR "
	
Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM Orders WHERE "&mlocx&" subdate BETWEEN #"+Request.Form("datebox")+"# AND #"+Request.Form("datebox2")+"# AND " & sWhere

Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()
Recordset1_numRows = 0 
%>

Select allOpen in new window