Nested Queries with a Parameter

[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

 

[John Vinson]

Anyone with an idea of how to get the prompting down to
only one time?

Try using a parameter of

=Forms!frmCrit!txtDate

and run the query from a form named frmCrit with an unbound textbox
named txtDate.

 

[WonderingWhy]

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.)

 

[John Vinson]

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.)

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.

 

[Mark Burns]

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

 

[Gary Walter]

Long shot...but just in case you are sorting
and don't have latest updates
(this problem was corrected in Microsoft Office 2000 SR-1/SR-1a.)

When You Sort Parameter Query Results You Are Prompted for Parameters Again
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q245467