Don't prompt for criteria

[Guest]

I have a query that pulls contact data for clients. Currently, I have users
run the query directly from a form into which they enter the data. This way,
I can limit the query by using an ID parameter from the form so that the
records are only for the particular client they just entered data for. I
would like them to have a 2nd option: To run the same query from a different
location, but based on a date rather than any particular client. The problem
is, I don't know how to bypass the pop-up box asking for the ID parameter I
set up for the first scenario. I want it to look for the field on the form,
but if the form is not open, I want it to skip to the next criteria without
prompting for the ID parameter. If the form is open, I want it to use the ID
parameter and not prompt for the date parameter.

Any help is appreciated!

 

[Jeff Boyce]

Tara

It sounds like you are using a popup form to gather the criterion for the
query.

Another approach would be to use the underlying form itself. And you could
use more than one criterion that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the reply Jeff, but I don't I expressed my needs very well. Let
me see if I can clarify. I want the user to be able to run the query for a
SPECIFIC client from the form where they just entered data. But, I want
supervisors to be able to run the query from a DIFFERENT form for ALL
clients. So in the 1st instance, I want the query to pull data based ONLY on
the ClientID parameter on the form. In the 2nd instance, I want supervisors
to pull data based on DATE rather than a specific ClientID. So, when the
query is run, it will look for the ClientID criteria. If it finds it, it
will NOT prompt me for the DATE criteria. If it does not find the ClientID
criteria, it will just kind of "skip" that criteria and look for (or prompt
for) the DATE criteria, and will NOT prompt me for the ClientID.

 

[Jeff Boyce]

Tara

What about using two different queries?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I could certainly do that, but I thought there might be a way to use just
one. It's no big deal just to copy the query and change the criteria, but I
was hoping to expand my knowledge if possible.

 

[Jeff Boyce]

Tara

"...expand my knowledge..." sounds a little like "... do it the way I think
it should work..." <g>!

(I tend to solve first, then look for a more 'elegant' solution...)

The problem, from my standpoint, revolves around your requirement to use
varying prompts, depending on what exists in the data. In fact, it sounds
like you want it to prompt for ClientID, but if it doesn't find it, to
prompt for Date info, and in that case, NOT prompt for ClientID -- I don't
see how it can both prompt and not prompt.

I'm not aware of ways within queries to do this, although there might be an
approach using the IIF() function.

If I were asked to set up something to allow for selection criteria of
Client and/or Date info, I'd use an unbound form, to gather the selection
criteria, and point the query to the form to get the criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP