Union query on a report

[Guest]

Hi All,
I created a report based on a union query. I'd like to filter the records
on the report by customer name. How can I set my union query to filter based
on selection from a form drop down. For example:


Query1
Select AcctNr, CusNr, CusName, Address
From NewCustomers
Where CusName Like "*" & [Forms]![frmCustomerSelection]![ddCusName] & "*"

Query 2
Select LoanNr, CusNr, CusName, Addr1 as Address
From ArchCustomers
Where CusName Like "*" & [Forms]![frmCustomerSelection]![ddCusName] & "*"


The report is based on the query below.
Select query1.*
From query1
Union all
Select query2.*
From query2

When I select the customer name whose records I want to look at it prompts
me a second time for the customer name. I am not sure what exactly I am
doing wrong.

 

[Douglas J. Steele]

If frmCustomerSelection is open, the query shouldn't be prompting you at
all.

If it's prompting you once, you've probably mistyped the name of the form or
the control in one of the two queries.

If it's prompting you twice and the form is open, then you've probably
mistyped it in both queries.

 

[Guest]

Hi Natalie,

Why don't you put the where statement in the last query statement which you
use as the source for your report. That way you only have one parameter. The
params you use now are identical so give it a try.

hth