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

Help creating a query

P: n/a
Hi Guys,

I need some help creating a query that is going to take between 1 and
10 parameters. The parameters are entered on a form into text boxes
that may have data or be empty. Is there a way to create a single
query that will take parameters if they are present in the text boxes
and not take parameters if the text box is empty?

The ten fields will need to be searched using "Like" so that the user
can search on part of a text string (all the fields are text).

With my limited knowledge of Access it seems that the only way I can
currently see is to create a single query for EACH version of
selections which would amout to about a 1000 queries (as I said, my
knowledge is somewhat limited in this area.).

Many thanks for any help, Dean...

Jul 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Yes, if you word the criteria correctly. It relies on a criteria of True
returning all records unless filtered on another field also.

Example:

SELECT ....
FROM ....
WHERE (Table1.Field1 = [Forms]![Form1]![Textbox1] Or
[Forms]![Form1]![Textbox1] Is Null);

--
Wayne Morgan
MS Access MVP
"DeanL" <de*************@yahoo.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
Hi Guys,

I need some help creating a query that is going to take between 1 and
10 parameters. The parameters are entered on a form into text boxes
that may have data or be empty. Is there a way to create a single
query that will take parameters if they are present in the text boxes
and not take parameters if the text box is empty?

The ten fields will need to be searched using "Like" so that the user
can search on part of a text string (all the fields are text).

With my limited knowledge of Access it seems that the only way I can
currently see is to create a single query for EACH version of
selections which would amout to about a 1000 queries (as I said, my
knowledge is somewhat limited in this area.).

Many thanks for any help, Dean...

Jul 7 '06 #2

P: n/a
I find it much more straighforward to create a "base query" without any
criteria, and then enhance its SQL in VBA code with criteria that I obtain
from the selection Form, using the enhanced SQL in the RecordSource of the
pertinent Form or Report, or in the WhereCondition of a DoCmd.OpenForm or
DoCmd.OpenReport.

An advantage of this approach is that you need do nothing to accomodate
returning all values, for selection criteria not entered on the Form -- you
just do not create selection criteria in the WHERE clause you are building.

Larry Linson
Microsoft Access MVP

"DeanL" <de*************@yahoo.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
Hi Guys,

I need some help creating a query that is going to take between 1 and
10 parameters. The parameters are entered on a form into text boxes
that may have data or be empty. Is there a way to create a single
query that will take parameters if they are present in the text boxes
and not take parameters if the text box is empty?

The ten fields will need to be searched using "Like" so that the user
can search on part of a text string (all the fields are text).

With my limited knowledge of Access it seems that the only way I can
currently see is to create a single query for EACH version of
selections which would amout to about a 1000 queries (as I said, my
knowledge is somewhat limited in this area.).

Many thanks for any help, Dean...

Jul 8 '06 #3

P: n/a
I agree with Larry, but you need to learn about VBA programming. In
VBA code, you can give a query any parameters and criteria a user
selects.

You need to learn about something called DAO to do this. And in DAO,
you need to learn about QueryDefs.

I learned this last year, and it radically changed many things about
the databases I've designed.

On 7 Jul 2006 14:53:17 -0700, "DeanL" <de*************@yahoo.com>
wrote:
>Hi Guys,

I need some help creating a query that is going to take between 1 and
10 parameters. The parameters are entered on a form into text boxes
that may have data or be empty. Is there a way to create a single
query that will take parameters if they are present in the text boxes
and not take parameters if the text box is empty?

The ten fields will need to be searched using "Like" so that the user
can search on part of a text string (all the fields are text).

With my limited knowledge of Access it seems that the only way I can
currently see is to create a single query for EACH version of
selections which would amout to about a 1000 queries (as I said, my
knowledge is somewhat limited in this area.).

Many thanks for any help, Dean...
Jul 8 '06 #4

P: n/a
In VBA programming you can make any query-string based on any
parameter. Without knowing anything on DAO or QueryDefs, you can assign
this query-string to Me.Recordsource to do the job, when opening the
form.
Donald Grove wrote:
I agree with Larry, but you need to learn about VBA programming. In
VBA code, you can give a query any parameters and criteria a user
selects.

You need to learn about something called DAO to do this. And in DAO,
you need to learn about QueryDefs.

I learned this last year, and it radically changed many things about
the databases I've designed.

On 7 Jul 2006 14:53:17 -0700, "DeanL" <de*************@yahoo.com>
wrote:
Hi Guys,

I need some help creating a query that is going to take between 1 and
10 parameters. The parameters are entered on a form into text boxes
that may have data or be empty. Is there a way to create a single
query that will take parameters if they are present in the text boxes
and not take parameters if the text box is empty?

The ten fields will need to be searched using "Like" so that the user
can search on part of a text string (all the fields are text).

With my limited knowledge of Access it seems that the only way I can
currently see is to create a single query for EACH version of
selections which would amout to about a 1000 queries (as I said, my
knowledge is somewhat limited in this area.).

Many thanks for any help, Dean...
Jul 8 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.