search candidates

[tomjones75]

dear community,

i want to search the content of all fields in one table in a access database.

it already works for the content of one field in the table.

please take a look at the code in the resultpage:

<%
Dim Keywords
Keywords = "%"
if (Request("search1")    <> "") then Keywords = Request("search1")   
%>
<%
MatchType = "AND"
If Keywords <> "" Then                         ' If there is something in the Keywords
    SearchFields = "kandidat"            ' The Database table column in which we are going to look for matches to our Keywords stored as a Text String in a Variable called SearchFields
    Keywords = Replace(Keywords, "'","''")     ' Replace any single quotes with 2 quotes, to stop the search failing, but allowing exact search to find matches with words with apostrophes. 
    If (MatchType <> "EXACT") Then    ' If Searchtype is not EXACT        
        Keywords = Replace(Keywords, ","," ")     ' Replace any commas, colons, semicolons, dashes, undersores,
        Keywords = Replace(Keywords, ":"," ")     ' forward slashes or back slashes in the text entered in the
        Keywords = Replace(Keywords, ";"," ")     ' Keywords text field with a space.
        Keywords = Replace(Keywords, "-"," ")
        Keywords = Replace(Keywords, "_"," ")
        Keywords = Replace(Keywords, "/"," ")
        Keywords = Replace(Keywords, "\"," ")
        WhereKeywordsString = " WHERE " & SearchFields & " LIKE '%" ' The SQL SELECT statement WHERE clause stored as a Text String in a Variable called WhereKeywordsString
 
        SearchArray = Split(Keywords," ")     ' Split the Keywords that are now separated by spaces and store them in an array
        For i = 0 to Ubound(SearchArray)     ' Repeat the following for each word in the array
            If i > 0 Then
                WhereKeywordsString = WhereKeywordsString & " " & MatchType & " " & SearchFields & " LIKE '%" & SearchArray(i) & "%'" ' Builds the SQL statement substituting AND / OR as defined by MatchType
            Else
            WhereKeywordsString = WhereKeywordsString & SearchArray(i) & "%'" ' If there is only one word in the array 
            End If    
        Next
    Else
    WhereKeywordsString = " WHERE kandidaten LIKE '%" & Keywords & "%'" 'If the SearchType selected was EXACT
    End If ' End If searchtype is not EXACT
End if    
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_itcag_STRING
    Recordset1.Source = "SELECT * FROM kandidaten" & WhereKeywordsString ' Here we've added or WhereKeywordsString variable
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
 

i would appreciate to get some help how i could search in another 3 fields.

thanks for your help.
regs tom

[MMcCarthy]

Tom

What is this written in?

Mary

[NeoPa]

This is quite a complicated and involved question.
I am not going to code this for you, but in principle, you need to build up the 'WhereKeywordsS tring' string to be just the part that is common to all the fields first.
Next you say something like :

WhereKeywordsString = _
    "WHERE ([kandidat]" & WhereKeywordsString & _
    ") OR ([ExtraField1]" & WhereKeywordsString & _
    ") OR ([ExtraField2]" & WhereKeywordsString & _
    ") OR ([ExtraField3]" & WhereKeywordsString & ")"

[NeoPa]

Tom

What is this written in?

Mary

Mary,
I'm not sure, but I think this may be VB Script code.
The relevant bits seemed close enough to VBA though so I worked on that basis.

-Adrian.

[MMcCarthy]

Mary,
I'm not sure, but I think this may be VB Script code.
The relevant bits seemed close enough to VBA though so I worked on that basis.

-Adrian.

Though it might be.

[tomjones75]

Tom

What is this written in?

Mary

hello mary,

it is written in a asp page.
regs tom

[tomjones75]

This is quite a complicated and involved question.
I am not going to code this for you, but in principle, you need to build up the 'WhereKeywordsS tring' string to be just the part that is common to all the fields first.
Next you say something like :

WhereKeywordsString = _
    "WHERE ([kandidat]" & WhereKeywordsString & _
    ") OR ([ExtraField1]" & WhereKeywordsString & _
    ") OR ([ExtraField2]" & WhereKeywordsString & _
    ") OR ([ExtraField3]" & WhereKeywordsString & ")"

-> THANKS. i will try that. have a good day, tom

[MMcCarthy]

hello mary,

it is written in a asp page.
regs tom

I'm going to post a pointer question in the ASP forum as they may be better able to help.

Mary

[hariharanmca]

This is quite a complicated and involved question.
I am not going to code this for you, but in principle, you need to build up the 'WhereKeywordsS tring' string to be just the part that is common to all the fields first.
Next you say something like :

WhereKeywordsString = _
    "WHERE ([kandidat]" & WhereKeywordsString & _
    ") OR ([ExtraField1]" & WhereKeywordsString & _
    ") OR ([ExtraField2]" & WhereKeywordsString & _
    ") OR ([ExtraField3]" & WhereKeywordsString & ")"

Tom is using search for string char (like '%" & strValue & "%') if he use the above code there may possibility of selecting all record in that table or qry

[NeoPa]

From post #1 :

SearchArray = Split(Keywords," ") 

strValue can only be empty if multiple spaces can find their way into Keywords.
If this is possible then you should look to handle it in your code.