Search Query

[primagic]

I have a form with textbox for searching records which populates a listbox. My SQL for the query is:

SELECT tblSchools.SchoolID, tblSchools.SchoolName, tblSchools.Phone, tblSchools.AddressLine1, Postcode.Postcode FROM tblSchools INNER JOIN Postcode ON tblSchools.PostcodeID=Postcode.PostcodeID WHERE (((tblSchools.SchoolID) Like "*" & Forms!frmSearch!Search2 & "*")) Or (((RemovePunc(tblSchools.SchoolName)) Like "*" & Forms!frmSearch!Search2 & "*")) Or (((tblSchools.Phone) Like "*" & Forms!frmSearch!Search2 & "*")) Or (((tblSchools.AddressLine1) Like "*" & Forms!frmSearch!Search2 & "*")) Or (((Postcode.Postcode) Like "*" & Forms!frmSearch!Search2 & "*"));

This search works good if I type in information from one field. I.e if i want to search on schoolname then i begin typing in the schoolname and it filters out the records.

However, what I want to be able to do is search on as many of the fields at once. I.e search by schoolname and postcode so it brings up anything with containing the schoolname and/or the postcode

How would I incorporate this into the SQL statement

Thanks

 

[dhookom]

What's wrong with your current SQL? Do you think you need 2 Search text boxes?

Do you want to give your users the opportunity to specify the "AND" or the "OR"?

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Yes. the users should be able to specify and or or. can this be done from the one textbox.

so if the records were

schoolname postcode
---------- --------
blue 2345
red 1278
green 2341
blue school 7894

so if someone type in: blue 234

it would retrieve

schoolname postcode
---------- --------
blue 2345
green 2341
blue school 7894

 

[dhookom]

Apparently you need a search text box for each searchable field.

Your example includes no specification regarding the AND/OR. Is this still a requirement?

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

yes it is still a requirement.

the example above points out that from what search box if someone types in blue 234 it retrieves records from two different fields. so it searches on either blue or 234 or both

 

[dhookom]

Again, you need more than one search text box. You also need some method of allowing the user to specify the AND/OR.

Think it all through with all of the possible scenarios and come up with a comprehensive specification. There are still questions that are not answered.

Hopefully you have a good ROI on this since you are possibly making a lot of programming/development for this.

Duane
Hook'D on Access
MS Access MVP