EricV wrote:
I have been looking for a solution to the following problem:
I have a databases with multiple searchable fields, and what I want to
do is to design a query which will enable me to enter various criteria
to search for, but if I leave some of the prompts blank (or null),
then I still want it to list the data that meets the criteria that was
entered.
I have a form that I want to do this through.
I have seen this problem posted before - and the solution lay in the
MS-Access Solutions Pack, but I have since realized that the postings
were from 1995 and the Solutions Pack was written back when windows
3.1 was the OS.
Does anyone have any suggestions?
Thanks
What I do is start with a basic SQL statement...
SELECT * FROM SomeTable Where 1 = 1
I store this in a variable strSQL and then I have code that examines each of the
search TextBoxes on the form thusly...
If IsNull(TextBoxForNumberSearch) = False Then
strSQL = strSQL & " AND SomeNumberField = " & Me.TextBoxForNumberSearch
End If
If IsNull(TextBoxForTextSearch) = False Then
strSQL = strSQL & " AND SomeTextField = '" & Me.TextBoxForTextSearch& "'"
End If
(continue for rest of TextBoxes)
At the end of the code I have a perfectly formatted SQL statement that only uses
the criteria from TextBoxes that actually contained an entry. Blank ones are
simply ignored. This avoids the inefficient use of having lots of " Or
Forms!FormName!ControlName Is Null" clauses in your query.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
