Searching Multiple Fields

[Guest_imported]

I have only recently started using access and I am attempting to create a search form that searches a Table with 14 fields. I want the user to be able to search using as many of the fields as possible. My problem is that I cannot manage to set it so that if some of the search criteria are left blank then it retrieves all the records. The only way I can manage at the moment is if every criteria is exactly the same as in the table.

 

[bitech]

Commod:

You and I have come into the same battle, but I have defeated this obstacle with a creativity. What I did was, set up tables for each of my Search Criterias. And when the SUBMIT button is hit on the search page, it will append the ID Numbers to each Criteria Table. My results page, Connects all of the Search Tables together, which will only produce the ID numbers of the company's that are in all of the criteria tables.

Example:

Tables:
SearchEmployees
SearchZip
SearchSales

Submit Button:
'E M P L O Y E E S I Z E
DoCmd.RunSQL "DELETE [SearchEmployee Size].ID FROM [SearchEmployee Size];"
If IsNull(Me![begin_employees]) = False And IsNull(Me![end_employees]) = False Then
DoCmd.RunSQL "INSERT INTO [SearchEmployee Size] ( ID )SELECT minority.ID FROM minority WHERE (((minority.Employees) Between [Forms]![Search]![begin_employees] And [Forms]![Search]![end_employees]));"
Else
'Append All Companies
DoCmd.RunSQL "INSERT INTO [SearchEmployee Size] ( ID ) SELECT Minority.ID FROM Minority;"
End If

'
'
'It appends all of the ID Numbers from my Minority table into the SearchEmployees Table if the controls on the Search page are Null or left blank.

 

[AArt]

You can do this with a regular parameter query, but with 14 fields it will look a little ugly. for example your criteria for field1 would be:
Like "*" & [Forms]![searchform]![field1] & "*"
Omit the "*" if you want exact matches, no wild cards.
Field2 would be:
Like "*" & [Forms]![searchform]![field2] & "*".
The part about leaving the search criteria blank has to do with whether you AND the criteria (put it all on 1 line in the query) or OR it (put each criteria statement on a seperate line).
I think. I'm not totally sure I understand the last part of your problem.
Once you get the hang of Parameter queries you'll love 'em.
Give me more detail and I'll see if I can be of further assistance.