Parameter Query choice?

  • Thread starter Thread starter Alex Marchant
  • Start date Start date
A

Alex Marchant

Hi,

is it possible to build a query so that a user can choose
a few parameters in order to see the results, and be able
to skip one of the parameters and move to the next one.

For example:-

If the parameters were to choose between two dates, and
choose an area, could one of these parameters (say
choosing an area) be skipped?

I know how to build normal partameter queries, but can
choices be bypassed??

Thanks in advance for any assistance,

regards,

Alex
 
Alex

Short answer: Yes, but...

Longer answer: That depends on how you want a "blank"/skipped parameter
handled. Are there valid and invalid combinations of present and skipped
parameters?

One way to handle a skipped parameter is to treat it as if there were no
restrictions. You can do that in the query by using something like the
following for the criterion (your syntax may vary):

Like * & [Enter Your Parameter] & *

This should have the effect of allowing any parameter (or none).

If your need is more complex, you'll want to consider creating an "order"
form with those fields to allow selection/entry of criteria. You will use
code behind this form to construct a SQL statement based on what's been
selected/entered.

Good luck!

Jeff Boyce
<Access MVP>
 
Alex,

Try using something like:

Like "*" & [Parameter] & "*"

as the criterion in the query instead of just [Parameter]. This will result
in the query returning all records if [Parameter] is null. It will also
result in the query returning all records where the field contains the
string in [Parameter] in any position, so it gives you the possibility to
run a partial match on the field, for example "foota" will return all
records where the field vontains foot, including foot, bigfoot, footage etc.
It is not a good idea to do it with dates, though; on a date field just use
a wide enough range to make sure it includes everything you want.

HTH,
Nikos
 
I'm dealing with the same issue, but use only a wildcard
on the end of my critera. I posted my question yesterday
on the newsgroup but didn't get a response. I'm doing fine
with one selection, but get a problem if I need multiple
selection (I take value from a filter form), that is, if
the user needs to select different values from the same
field. I have three (or however many) fields on my form to
select the different values from the field "Positions." I
understand that the wildcard in every choice cancels the
other choice, but don't know how to construct it
otherwise. Would you be kind enough to look at my post
from September 23, Re: Multiple selection criteria with
wildcard. I really need help with this.
Thanks.
Brigitte P.
 
If you want an exact match then try the following

FieldA Like Forms!Formname!ControlName1 & ""
Or
FieldA Like Forms!Formname!ControlName2 & ""
Or
FieldA Like Forms!Formname!ControlName3 & ""

A partial match, use the NZ function and an "impossible" value - I've used
"XYXTTSEW" in the example.

[Positions] Like Nz(Forms!Formname!ControlName1,"XYXTTSEW") & "*"
Or
[Positions] Like Nz(Forms!Formname!ControlName2,"XYXTTSEW") & "*"
Or
[Positions] Like Nz(Forms!Formname!ControlName3,"XYXTTSEW") & "*"

I'm dealing with the same issue, but use only a wildcard
on the end of my critera. I posted my question yesterday
on the newsgroup but didn't get a response. I'm doing fine
with one selection, but get a problem if I need multiple
selection (I take value from a filter form), that is, if
the user needs to select different values from the same
field. I have three (or however many) fields on my form to
select the different values from the field "Positions." I
understand that the wildcard in every choice cancels the
other choice, but don't know how to construct it
otherwise. Would you be kind enough to look at my post
from September 23, Re: Multiple selection criteria with
wildcard. I really need help with this.
Thanks.
Brigitte P.
-----Original Message-----
Alex

Short answer: Yes, but...

Longer answer: That depends on how you want a "blank"/skipped parameter
handled. Are there valid and invalid combinations of present and skipped
parameters?

One way to handle a skipped parameter is to treat it as if there were no
restrictions. You can do that in the query by using something like the
following for the criterion (your syntax may vary):

Like * & [Enter Your Parameter] & *

This should have the effect of allowing any parameter (or none).

If your need is more complex, you'll want to consider creating an "order"
form with those fields to allow selection/entry of criteria. You will use
code behind this form to construct a SQL statement based on what's been
selected/entered.

Good luck!

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Back
Top