Filter SQL query by criteria

[IdleJack]

Wow my second post and I still need help!



Working on a customer management dbase and I wrote my first ever SQL query today! The query I wrote is a union query as follows:

SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_DRIVEWAYS Query]
UNION ALL
SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_PRODUCTS Query]
UNION ALL SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_SOLAR]

What I am trying to figure out now is how to put in a couple of filter criteria, so as when the query is run it asks for specific criteria, on the following common fields:

CustomerAdvisor (a single value), SoldDate ('From'-'To' dates)

Any help is appreciated.

David

[RayMilhon]

Below Each From put the following:

Where CustomerAdvisor = [pcustomerAdvisor] and Solddate between [startdate] and [enddate]

When you run the query you should be prompted 3 times for pcustomerAdmisor, startdate and end date.

[IdleJack]

Below Each From put the following:

Where CustomerAdvisor = [pcustomerAdvisor] and Solddate between [startdate] and [enddate]

When you run the query you should be prompted 3 times for pcustomerAdmisor, startdate and end date.

Hi Ray,

I entered the above as suggested, modified it a little, but I'm now prompted 3 X for each query that is in the union query; 3 X for each prompt. Below is the modified SQL

SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_DRIVEWAYS Query]
Where CustomerAdvisor = [Enter Customer Advisor] and Solddate between [Start Date dd/mm/yy] and [End Date dd/mm/yy]
UNION ALL
SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_PRODUCTS Query]
Where CustomerAdvisor = [Enter Customer Advisor] and Solddate between [Start Date dd/mm/yy] and [End Date dd/mm/yy]
UNION ALL
SELECT CustomerAdvisor, Outcome, SoldDate, SoldSameMonth, Product, LeadSource
FROM [2_AppointMasterSELECT_SOLAR]
Where CustomerAdvisor = [Enter Customer Advisor] and Solddate between [Start Date dd/mm/yy] and [End Date dd/mm/yy]

Any suggestions as to how I only have to enter the info once?

Thanks