By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,315 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Multiple Field Queries

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.