Parameters + Union queries + Order By = Unexpected Result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Somebody please help me with this perplexing problem.

I am trying to return the union of two queries, both of which use the
same parameters. When I don't try to sort the results, Access asks me
for the parameters one time each -- as I would expect. However, when I
add an Order By clause and execute the query, Access asks me for both
parameters twice. Why??? This is OK by me but my end users will
revolt.

Here is the form of the queries I'm using. The top level query is:

Query GetUnionOfTwoThings:

PARAMETERS
[Threshold] Currency,
[SomeParameter] Int;
Select *
From
GetStuffBelowThreshold
UNION
GetStuffAboveThrehold
ORDER BY
A,
B

.... and the subqueries are:

Query GetStuffAboveThreshold:

Select
ThisField as A,
ThatField as B
From
WhateverTable
Where
ThatField>[Threshold]


Query GetStuffBelowThreshold:

Select
Sum(ThisField) as A,
IIF(ThatField=999,999,ThatField/[SomeParameter]) as B
From
WhateverTable
Where
ThatField<=[Threshold]
Group By
ThisField,
IIF(ThatField=999,999,ThatField/[SomeParameter])

Thanks ... I'd really appreciate the help.

Regards,
-Gary Wolf
 
I am trying to return the union of two queries, both of which use the
same parameters. When I don't try to sort the results, Access asks me
for the parameters one time each -- as I would expect. However, when I
add an Order By clause and execute the query, Access asks me for both
parameters twice. Why??? This is OK by me but my end users will
revolt.
Click to expand...

You might want to consider using a Form to solicit parameters, rather
than prompts; use criteria like

[Forms]![YourFormName]![YourControlName]

Put a command button on the form to launch the report or whatever else
you're doing with the union query.

Not sure why you're getting the two prompts though!

John W. Vinson[MVP]
 
Back
Top