Dijunctive searches

[lechip]

So I'm developing an application where disjunctive searches are required, i.e:
Someone looks for a document. Yet the person could have its ID and the title OR the Author and the Project of the document, OR more data related.

How have you guys dealt with such scenarios in the past? have any tips to improove performance?

Tnx!

 

[Lightwave]

I tend to have a single search field on a list box

And run a select query looking for a string within any of a limit set of fields eg 5 based on the "on change event".

So if I were to have three fields
ID
Project
Author

and I typed 123

it would show
123 house Madonna
1234 shop Sting
1235 car Ferrari
1238 shed Bill
etc...

On typing 1 there would be a larger list that is reduced as you type 2 and 3
Clicking on the line on the list takes you to the record. Depending on the string and the number of records and similarity of your records the amount of digits or letters required to be typed to find the record will vary but it is rare that you need to type the whole string in the applications I use.

So the code in a CRM database I've made is as follows

Private Sub Text2_Change()

Dim strSQL As String
Dim txtSearchString As String

txtSearchString = Me!Text2.Text

strSQL = "SELECT [Q032FindName].[PKID], [Q032FindName].[Name], [Q032FindName].[Position], [Q032FindName].[Vocation], [Q032FindName].[CompanyName] FROM [Q032FindName]"
strSQL = strSQL & "WHERE ((([Q032FindName].[Name]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[CompanyName]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[Position]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[Vocation]) Like '*" & txtSearchString & "*') OR (([Q032FindName].[PKID]) Like '*" & txtSearchString & "*'))"

Me!List11.RowSource = strSQL
Me!List11.Requery


End Sub

So this looks for the searchstring * which is input into a text box named Text2 it looks for this in any of 5 fields. Name, Company Name, Position, Vocation or PKID. Anytime a new character is typed or deleted the list is rerun

 

[lechip]

its an interesting way, will keep it in mind, thanks Lightwave!