Nested Queries with a Parameter

  • Thread starter Thread starter WonderingWhy
  • Start date Start date
W

WonderingWhy

I am trying to build a set of nested queries where a
parameter is passed. I have four saved queries.
1) Query1 is a SELECT of certain records from Query2. Note
Query1 itself doesn't require any parameters.
2) Query2 is a complicate JOIN of two other queries (#3
and #4.) It also doesn't directly need any parameters.
3) Query3 is a DISTINCT SELECT on an existing table based
on a date. This date is the parameter, [Enter Date], the
user provides at runtime.
4) Query4 is a fairly simple SELECT which also does not
require any parameters.

As you can see, there is only one parameter which is only
required in one query. But when I run Query1 (thus calling
the other nested ones), I get prompted for the parameter,
[Enter Date], three times when it should only be once.

I have tried not declaring the parameter, [Enter Date]. I
have tried only delaring it in Query3. I have tried
declaring the parameter in All the queries. And maybe a
couple of other ways.

It seems no matter how/if I declare the parameter, I get
prompted three times to enter the date. I have done this
kind of thing before but I haven't seen this problem
before. Does anyone have a clue what I am doing wrong?
Anyone with an idea of how to get the prompting down to
only one time?

TIA,
WonderingWhy
 
Anyone with an idea of how to get the prompting down to
only one time?
Click to expand...

Try using a parameter of

=Forms!frmCrit!txtDate

and run the query from a form named frmCrit with an unbound textbox
named txtDate.
 
Thanks. That is essentially what the final result is going
to be (in my case probably a form with VBA code where VBA
calls the top query) but I was worried that I was doing
something wrong which would bite me later. Is this some
kind of known bug/feature in Access2000?

What is really weird is that going along and adding more
functionality which I found would be needed (basically
adding a LEFT JOIN clause between Query4 and an existing
table), and suddently it started working correctly!?!?

Any ideas why it suddenly started working? (BTW, I am
declaring the parameter only one time as part of the query
that actually needs it.)
 
What is really weird is that going along and adding more
functionality which I found would be needed (basically
adding a LEFT JOIN clause between Query4 and an existing
table), and suddently it started working correctly!?!?

Any ideas why it suddenly started working? (BTW, I am
declaring the parameter only one time as part of the query
that actually needs it.)
Click to expand...

Without seeing (and spending I'd guess a LOT more time than I have
free right now analyzing) the SQL of each stage of these queries, I'd
have no clue.
 
John,

FYI, I too have seen this sort of behavior - in Access XP as well.

WonderingWhy, In my experience, when I've seen queries begin behaving this
way, occasionally a Compress & Repair has made things work properly again
....and sometimes not.
When it doesn't work, going the create-a-new-.MDB-and-import-everything
generally has worked to make the query-parameter-prompting behavior again
work as expected (one prompt per parameter). Doing that last option, of
course, would present a big problem for a replicated .mdb...

- Mark
 
Back
Top