Make Query Parameters Optional

[Guest]

Is there a way to make query parameters optional when it is invoked from code?

For example, a query has been defined to accept two parameters. They are
Country and State. During its execution from code, I would like to able to
indicate the two parameters are optional, such that, all records are
returned.
At other times, they are required.

 

[Duane Hookom]

Is there a good reason to have any parameters/criteria in the query? I
rarely use dynamic criteria in queries since the queries are viewed as the
record sources of either forms or reports. The "where" clause in DoCmds work
great for applying any criteria I need.

 

[G. V.]

Yes, use Like [Enter Country] & "*" in the criteria for Country and Like
[Enter State] & "*" for the criteria entry under State.
Pressing enter at both should return all records. Having said the previous,
if you need the Country or State as optional enters, then you will have to
put the State statement on the "OR" section of the criteria.

You will have to experiment what works best. Also the user does not need to
type in the entire Country or State name with the & "*" as whatever they
type is handled, such as United St will return United States. If they type
just United, they may get United States and United Arab Emirates (sp?).

 

[Bonnie]

Yes, Duane, there really is. When you are in the first few
years of learning, queries are about as brave as you get.
Then you depend on them. I'm just now learning how to
use "where"s. And it is thanks to folks like you that I am
learning that there is much more potential to Access once
you break the VB/Code barrier. Please be patient with us.

 

[Duane Hookom]

I am not always considerate of others' knowledge levels. However in this
case Charles stated "invoked from code". This lead me to believe he might
have been creating a recordset or at the very least was familiar with
writing basic code. I generally allow the wizard to write my OpenReport or
OpenForm code and then go back and add a few more lines to create the where
clause.

This solution makes my reports much less dependant on form controls or user
input.

 

[Guest]

Thanks for your information.

Yes, use Like [Enter Country] & "*" in the criteria for Country and Like
[Enter State] & "*" for the criteria entry under State.
Pressing enter at both should return all records. Having said the previous,
if you need the Country or State as optional enters, then you will have to
put the State statement on the "OR" section of the criteria.

You will have to experiment what works best. Also the user does not need to
type in the entire Country or State name with the & "*" as whatever they
type is handled, such as United St will return United States. If they type
just United, they may get United States and United Arab Emirates (sp?).

Is there a way to make query parameters optional when it is invoked from code?

For example, a query has been defined to accept two parameters. They are
Country and State. During its execution from code, I would like to able to
indicate the two parameters are optional, such that, all records are
returned.
At other times, they are required.

 

[Guest]

Thanks for your information.

Is there a good reason to have any parameters/criteria in the query? I
rarely use dynamic criteria in queries since the queries are viewed as the
record sources of either forms or reports. The "where" clause in DoCmds work
great for applying any criteria I need.