In my search window I have 5 text fields:
NumberTextField
TypeTextField
CombinedDateTextField
InvolvedTextField
DealingTextField
In my database I have the following accompanying columns:
Number
Type
Date
Involved
Dealing
I am trying to allow the user to enter as little or as much search criteria as desired, and then have the listbox in my main window update to show ONLY the results which match exactly to the search criteria.
Example:
If the user enters only 1 search criteria such as “Fire” in the TypeTextField, it will display all rows with the word “fire” in the type column of the database.
If however, the user enters “Fire” in the TypeTextField, AND “David” in the InvolvedTextField, it should display all database rows where Type = Fire AND Involved = David
I have the code below, but my concatenation code segment gets all jumbled up.
Could anyone please help?
[code]Dim Search As String
Dim TextFieldControlArray() as TextField
// CREATE AND POPULATE THE TextFieldControlArray TO MATCH THE WINDOW’S TEXTFIELDS
TextFieldControlArray.Append NumberTextField
TextFieldControlArray.Append TypeTextField
TextFieldControlArray.Append CombinedDateTextField
TextFieldControlArray.Append InvolvedTextField
TextFieldControlArray.Append DealingTextField
// CREATE AND POPULATE THE FieldNamesArray TO MATCH THE DATABASE COLUMN NAMES
Dim FieldNamesArray(5) as String
FieldNamesArray(0) = “Number”
FieldNamesArray(1) = “Type”
FieldNamesArray(2) = “Date”
FieldNamesArray(3) = “Involved”
FieldNamesArray(4) = “Dealing”
// SET SearchCriteria TO EMPTY
Dim SearchCriteria as String = “”
// LOOP THROUGH THE TextfieldControlArray LOOKING FOR ENTERED SearchCriteria
for i as Integer = 0 to UBound(TextfieldControlArray)
if TextfieldControlArray(i).text <> “” then
// ---------------------------------------- THIS BLOCK OF CODE SEEMS TO BE WRONG
// CONCATENATE MULTIPLE SearchCriteria ENTERED BY THE USER (IF NECESSARY)
SearchCriteria = TextfieldControlArray(i).text
SearchCriteria = “LIKE '%”+SearchCriteria+"%’ AND "
for p as integer = 0 to UBound(FieldNamesArray)
Search = Search+FieldNamesArray§+" "+SearchCriteria
next
end if
next
// REMOVE THE LAST AND FROM THE COMBINED SearchCriteria
If right(Search,4) = "AND " then
Search = left(Search,len(Search)-5)
end if
// RETRIEVE RECORDSET RESULTS FROM THE DATABASE
Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("SELECT * FROM Incidents WHERE " +Search + “ORDER BY IRef ASC”)
IncidentsRS = ps.SQLSelect
// NO MATCHING RECORDS FOUND, SO DISPLAY AN ERROR MSGBOX
if IncidentsRS = Nil or IncidentsRS.RecordCount <1 then
MsgBox(“No matching records found!”)
End if[/code]
Alternatively, is it possible to achieve this by using just an SQLSelect statement somehow???
The problem is that I do not know how many search criteria will be entered by the user?
Thank you all in advance.
