By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

search candidates

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim Keywords
  3. Keywords = "%"
  4. if (Request("search1")    <> "") then Keywords = Request("search1")   
  5. %>
  6. <%
  7. MatchType = "AND"
  8. If Keywords <> "" Then                         ' If there is something in the Keywords
  9.     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
  10.     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. 
  11.     If (MatchType <> "EXACT") Then    ' If Searchtype is not EXACT        
  12.         Keywords = Replace(Keywords, ","," ")     ' Replace any commas, colons, semicolons, dashes, undersores,
  13.         Keywords = Replace(Keywords, ":"," ")     ' forward slashes or back slashes in the text entered in the
  14.         Keywords = Replace(Keywords, ";"," ")     ' Keywords text field with a space.
  15.         Keywords = Replace(Keywords, "-"," ")
  16.         Keywords = Replace(Keywords, "_"," ")
  17.         Keywords = Replace(Keywords, "/"," ")
  18.         Keywords = Replace(Keywords, "\"," ")
  19.         WhereKeywordsString = " WHERE " & SearchFields & " LIKE '%" ' The SQL SELECT statement WHERE clause stored as a Text String in a Variable called WhereKeywordsString
  20.  
  21.         SearchArray = Split(Keywords," ")     ' Split the Keywords that are now separated by spaces and store them in an array
  22.         For i = 0 to Ubound(SearchArray)     ' Repeat the following for each word in the array
  23.             If i > 0 Then
  24.                 WhereKeywordsString = WhereKeywordsString & " " & MatchType & " " & SearchFields & " LIKE '%" & SearchArray(i) & "%'" ' Builds the SQL statement substituting AND / OR as defined by MatchType
  25.             Else
  26.             WhereKeywordsString = WhereKeywordsString & SearchArray(i) & "%'" ' If there is only one word in the array 
  27.             End If    
  28.         Next
  29.     Else
  30.     WhereKeywordsString = " WHERE kandidaten LIKE '%" & Keywords & "%'" 'If the SearchType selected was EXACT
  31.     End If ' End If searchtype is not EXACT
  32. End if    
  33. %>
  34. <%
  35. set Recordset1 = Server.CreateObject("ADODB.Recordset")
  36. Recordset1.ActiveConnection = MM_itcag_STRING
  37.     Recordset1.Source = "SELECT * FROM kandidaten" & WhereKeywordsString ' Here we've added or WhereKeywordsString variable
  38. Recordset1.CursorType = 0
  39. Recordset1.CursorLocation = 2
  40. Recordset1.LockType = 3
  41. Recordset1.Open()
  42. Recordset1_numRows = 0
  43. %>
  44.  
i would appreciate to get some help how i could search in another 3 fields.

thanks for your help.
regs tom
Feb 7 '07 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Tom

What is this written in?

Mary
Feb 8 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 'WhereKeywordsString' string to be just the part that is common to all the fields first.
Next you say something like :
Expand|Select|Wrap|Line Numbers
  1. WhereKeywordsString = _
  2.     "WHERE ([kandidat]" & WhereKeywordsString & _
  3.     ") OR ([ExtraField1]" & WhereKeywordsString & _
  4.     ") OR ([ExtraField2]" & WhereKeywordsString & _
  5.     ") OR ([ExtraField3]" & WhereKeywordsString & ")"
Feb 8 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 8 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Feb 8 '07 #5

P: 3
Tom

What is this written in?

Mary
hello mary,

it is written in a asp page.
regs tom
Feb 8 '07 #6

P: 3
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 'WhereKeywordsString' string to be just the part that is common to all the fields first.
Next you say something like :
Expand|Select|Wrap|Line Numbers
  1. WhereKeywordsString = _
  2.     "WHERE ([kandidat]" & WhereKeywordsString & _
  3.     ") OR ([ExtraField1]" & WhereKeywordsString & _
  4.     ") OR ([ExtraField2]" & WhereKeywordsString & _
  5.     ") OR ([ExtraField3]" & WhereKeywordsString & ")"

-> THANKS. i will try that. have a good day, tom
Feb 8 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 8 '07 #8

hariharanmca
100+
P: 1,977
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 'WhereKeywordsString' string to be just the part that is common to all the fields first.
Next you say something like :
Expand|Select|Wrap|Line Numbers
  1. WhereKeywordsString = _
  2.     "WHERE ([kandidat]" & WhereKeywordsString & _
  3.     ") OR ([ExtraField1]" & WhereKeywordsString & _
  4.     ") OR ([ExtraField2]" & WhereKeywordsString & _
  5.     ") 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
Feb 9 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
From post #1 :
Expand|Select|Wrap|Line Numbers
  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.
Feb 9 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.