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
9  2226  MMcCarthy 14,534
Recognized Expert Moderator MVP
Tom
What is this written in?
Mary
NeoPa 32,556
Recognized Expert Moderator MVP
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 : - WhereKeywordsString = _
-
"WHERE ([kandidat]" & WhereKeywordsString & _
-
") OR ([ExtraField1]" & WhereKeywordsString & _
-
") OR ([ExtraField2]" & WhereKeywordsString & _
-
") OR ([ExtraField3]" & WhereKeywordsString & ")"
NeoPa 32,556
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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.
Tom
What is this written in?
Mary
hello mary,
it is written in a asp page.
regs tom
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 :
- WhereKeywordsString = _
-
"WHERE ([kandidat]" & WhereKeywordsString & _
-
") OR ([ExtraField1]" & WhereKeywordsString & _
-
") OR ([ExtraField2]" & WhereKeywordsString & _
-
") OR ([ExtraField3]" & WhereKeywordsString & ")"
-> THANKS. i will try that. have a good day, tom
MMcCarthy 14,534
Recognized Expert Moderator MVP
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
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 :
- 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 32,556
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Case Nelson |
last post by:
Hi there I've just been playing around with some python code and I've
got a fun little optimization problem I could use some help with.
Basically, the program needs to take in a random list of no...
|
by: dave |
last post by:
I am wondering if the following can be done.
I want to setup a search page that utilizes full text
searching in sql2000. I want the user to type in
say "where is bill" and have the query search...
|
by: Jeff |
last post by:
Its been years since I did C++ and Im unsure what this is about.
I have a QT class Im inheriting from in my .h file:
class Mosfet : Q3CanvasRectangle
{
public:
Mosfet::Mosfet(const QRect...
|
by: ravi.malashetty |
last post by:
RAYLOGIX TECHNOLOGIES is now providing LIVE PROJECTS for M.TECH/B.E/
MCA/BCA (CSE/ISE/ECE)final year candidates.Interested candidates can
directly come to the company.
„« WE GOT MORE THEN 20 LIVE...
|
by: finalyearproject6 |
last post by:
RAYLOGIX TECHNOLOGIES is now providing LIVE PROJECTS for M.TECH/MCA/
BCA/B.Sc/M.Sc/B.E (CSE/ISE/ECE)final year candidates.Interested
candidates can directly come to the company.
„« WE GOT MORE...
|
by: HariKutty |
last post by:
Hi everybody,
My Probs is
I am having a table -RESUME
It contains two columns
CandidateID ResumeTitle
101 - 0 to 4 yrs of Exp in JAVA
102 - 1 t0 8 yrs of...
|
by: Career Point |
last post by:
Perot, IBM, Hewlett-Packard, Dell, Wipro, HCL, Infosys, Satyam Hiring
software Engineering candidates and MBA candidates
Perot Walk-in: Aug 1-7 | Survik (MNC) Walk-in | E-Edit Solutions |
Zenith...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
