Searching Multiple Fields From Text Box?

[philljp390]

I have a table of addresses, and I want to be able to search about 4 fields in this table to find a building name, for example.

I want to be able to do this via a form, with a text box. The user enters in the building name, clicks 'search'. Then the results could be displayed in a query or list box.

Any help appriciated.

 

[Ukraine82]

Make a query based on a table. In the criteria for the search field, enter:

Like“*”&[Forms]![Myform]![txtSearchCriteria]&”*”

Replace “MyForm” with whatever the name of your form that you wish to insert your Search Field.

Save the Query to any name you wish. In form add a text box control, and give it the name txtSearchCriteria.

In the txtSearchCriteria field, add the formula below in After Update event:

Private Sub txtSearchCriteria_AfterUpdate() DoCmd.OpenQuery "MyQuery" End Sub

Michael

 

[philljp390]

Thanks, but this would only search in 1 field though wouldn't it? I want to search multiple fields, around 6 fields.

 

[ColinEssex]

Look in the sample databases. There's a Quicksearch Db there posted by Parker that does this. You may need to amend it a bit.

Col

 

[philljp390]

The only one i could find is:

http://www.access-programmers.co.uk/forums/showthread.php?t=62853

by "jfgambit"

This only searches one field.

Update: found this:

http://www.access-programmers.co.uk/forums/showthread.php?t=65331&highlight=quicksearch

I'll take a look now

 

[philljp390]

ok, i got Michael's (Ukraine82) suggestion working.

Is there actually anyway to speed the search up, as im searching a lot of records?

 

[Ukraine82]

Try placing: Like“*”&[Forms]![Myform]![txtSearchCriteria]&”*” in a criteria field(query) that can help you reduce duplicate records or show less records.


Michael

 

[philljp390]

ok, as an extension of this. I now have 1 text box called "Generic address" which is searching 6 fields to find the strings entered (Based on Michael's idea).

It would be handy to keep this text box on the form, and add another one called "Town". The contents of this text box just searches in the Town field, while the "Generic Address" text box searches 6 fields.

For example:

Generic Address Field: City Hall
Town: London

This would then display the results for addresses containing "City Hall" in "London"

Any suggestions?

 

[philljp390]

I have got a bit further with this now

Im using a search from the sample database:

http://www.access-programmers.co.uk/forums/showthread.php?t=62853

I have adapted one form so that it has 2 text boxes:

txtDetails and txtCity

The current code, which works, is this:

If Not IsNull(Me.txtDetails) Then
strWhere = strWhere & " (tblInfo.Company) Like '*" & Me.txtDetails & "*' AND"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (tblInfo.City) Like '*" & Me.txtCity & "*' AND"
End If

However, I would like txtDetails to search more than one field. Something along the lines of:

If Not IsNull(Me.txtDetails) Then
strWhere = strWhere & " (tblInfo.Company) OR (tblInfrganisation) Like '*" & Me.txtDetails & "*' AND"
End If

This does not work, but hopefully someone can suggest a solution?

 

[Brianwarnock]

Hi
You should go into EDIt and delete this last post as you have also posted it in Modules and VBA

Brian