Increase Search Efficiency

Brando

Enthusiastic Novice
Local time
Today, 02:12
Joined
Apr 4, 2006
Messages
100
Hi,

I am more or less a beginner in Access. In my db, I have a search form with two input criteria (cboYear and txtInput) that populate a hidden text box (txtSearchString). The search results are displayed in a listbox (lstSummary) based on a query (qrySearch). The OnChange events allow updated results on each keystroke. The user selects the year of the search and then inputs text. The criteria in each field of qrySearch includes:

Like "*" & [Forms]![frmSearch]![txtSearchString] & "*"

This worked great for the first 2 or 3 years. However, as the years go by (I built this in ’06), it is increasingly cumbersome to manually search each year. I am hoping to find a fix that won’t require a total do-over like building a filter. If I want the search results to include matches from all years, is there a way for the search to happen with nothing in cboYear? In other words, it would search all years. Below is the relevant code. Thank you for your help.


Private Sub cboYear_Change()
Dim vSearchString As String
vSearchString = cboYear.Text

txtSearchString.Value = vSearchString
Me![Summary].Requery

End Sub

Private Sub txtInput_Change()
Dim vSearchString As String
vSearchString = txtInput.Text

txtSearchString.Value = vSearchString
Me![Summary].Requery

End Sub

Private Sub txtSearchString_Change()
Dim vSearchString As String
vSearchString = txtInput.Text & cboYear.Text

txtSearchString.Value = vSearchString
Me![lstSummary].Requery

End Sub
 
Thank you for your help. Seems simple enough, although it probably prevents the ability to use both criteria at the same time.

Per your suggestion, I replaced "&" with "Or" in the OnChange event of txtSearchString. Also, I added ";" to the front of the list of years in the Row Source property of cboYear. However, when I select the blank year and type into txtInput, the search no longer produces any results. Any ideas?
 
How about having a separate listbox with all the years in your database?
 
I am not sure if this will help - here is something I wrote and used in many databases that I've thrown together. Although it uses filtering (and not query driven), it might help clue you in where you are or provide an altogether new solution.

Even though it doesn't do any true fuzzy searching, it searches all of the columns of your query and gives the appearance that it is doing some fancy-schmancy stuff because of what you can with a single text control and a drop-down (essentially managing the " and Likes behind the scenes). This is also because the users that I am used to seeing aren't requiring some awesome googling way to search.

You can take this and expand on it. For instance adding a drop down box to the right with the years in it to AND it to the filter or just making the 06 part of the search.

Anyhow, hope that helps.

-dK
 

Attachments

Last edited:
In trying to increase efficiency, is there a way to use an IIF statement in the querry? It would say: give me all results where (whatever field) is like txtSearchString AND (if cboYear is greater than 0), where Date = cboYear. However, if cboYear is empty (null), then just search using textSearchString.

The idea is to be able to do a keyword search by year, or if left blank, a keyword search on all years.

Here is me taking a stab at it:

SELECT tblData.[No], tblData.Client, tblData.ClientNo, tblData.Address, tblData.Date
FROM tblData
WHERE (((tblData.Client) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*") AND ((IIf([Forms]![frmSearch]![cboYear]>0,[Forms]![frmSearch]![cboYear]))=Year([Requested])))
OR (((tblData.ClientNo) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*") AND ((IIf([Forms]![frmSearch]![cboYear]>0,[Forms]![frmSearch]![cboYear]))=Year([Requested])))
OR (etc. etc....)

I gave this a try and it didn't work. Am I close or is this a chimp that won't swing? (monkey humor)

Thank you,
Brando
 
Try ...

Code:
SELECT tblData.[No], tblData.Client, tblData.ClientNo, tblData.Address, tblData.Date
FROM tblData
WHERE (((tblData.Client) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*") AND (([COLOR=red]tblData.Requested[/COLOR]) Like "*" & [Forms]![frmSearch]![cboYear] & "*"))

I made the bit in red because I am unsure what field from the tblData you are trying to match against. If it is a standard (short) date, you could get away with '07', or, '06', etc., even if it was a regular text control. The empty combo box should return all records.

That is, it worked for me with a combo box. I used a value list with the years in it and tried the shorter year terms (and null).

HTH,
-dK
 
A brilliant, yet ellegant solution. The search engine works better than ever! Thank you for all of the help!
 
You betcha. Good luck on your projects!

-dK
 

Users who are viewing this thread

Back
Top Bottom