Parameters in Union Query 2

[MelF]

I have this query:

SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery1]

UNION SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery2];

I want to add two parameters: One that prompts for date range (between1-1-00 and 1-28-00), and one that prompts for product name. I've tried examples that I've seen and nothing is working. Anyone know how to fix this??

Thanks!!!

 

[MichaelRed]

Not in detail, but realize that 'parameter' queries end up just being queries w/ "WHERE" clauses, so just add the appropiate Where clause(s) in the querie(s). You need to do it (WHERE) for EACH "Select" clause.

So it looks like:

Select ....
Form ....
Where .....

Union
Select ....
From ....
Where ...

And so on.

Actually, the Where clauses do not NEED to be the same, however our statement indicates that they probably will be for the specific situation.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MelF]

MICHAEL - O.K., I've been reading about WHERE statements, but because this is a union query, I don't know what the syntax will be. I know this isn't write, can you tell me exactly what the code should be:

SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery1]
WHERE (OrderDate.Between[Enter begin date:] And[Enter end date:]

UNION SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery2]
WHERE (OrderDate.Between[Enter begin date:] And[Enter end date:];

Thanks a lot!!!

 

[MichaelRed]

Actually, I think it is very close. I see a couple of un-matched parenthesis:


SELECT [OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM [UnionQuery1]
WHERE (OrderDate.Between [Enter begin date:] And [Enter end date:])[/b}

UNION SELECT [OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM [UnionQuery2]
WHERE (OrderDate.Between [Enter begin date:] And [Enter end date:]);

These are indicated by the ADDITION of ) where I think the are necessary. I also added a few spaces to seperate KeyWords from the arguments. With out a set of tables to test it on, this is about as far as I can take your specific request.

I duped a sample table in one of my teaching databases and did a Union query on the duplicated table. Teh SQL is shown below. It is not EXACTLY the same, as I did not use the two parameters, but just placed the criteria directly in the SQL statement, however this should make no real difference in the process. Examine the SQL below and compare it to the one above. I believe the only differences are the spaces, the Parameters and the inclusion in the one below of the KeyWord ALL. this last difference MAY be of use to you, and you certainly need to understand it's function. Without the KeyWord "ALL" in the Union query, it -UNLIKE MOST SQL CLAUSES- will remove Duplicate Records. So, IF you wnat ALL records from the Various Sources, - even if they are duplicates, You MUST include the ksyword "ALL". If you have MUltiple "Unions", you need to Include it with EACH Union Clause.


SELECT StuName, ASgn01, Asgn03 From tblGrade Where StuName = "Michael Red"
Union all SELECT StuName, ASgn01, Asgn03 From tblGrade Where StuName = "Michael Red";

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MelF]

MICHAEL - Thanks for your help. In the following code line:

WHERE (OrderDate.Between [Enter begin date:] And [Enter end date:])[/b}

Just want to make sure that [/b} is correct. (I've never seen this before. I typed it in and it didn't work.

As for the keyword "all" - My query appears to be returning all the correct records, so I don't think I need this keyword. If I can just get it to restrict to specified parameters . . .

Thanks again!!

 

[MichaelRed]

no, the )&*^(&*@^#&@^# TYPO fingers got me again. the "[/b}" SHOULD have been [/b] which you wouldn't have seen at all. It is part of the markup language (turns bold off). Sorry for the error on my part, coonfusion on mine!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MelF]

MICHAEL - Thanks. I tried your code again, w/o that "b", and it's still not working. I'll have to keep looking into it.

Thanks again!

 

[MichaelRed]

I meant the whold 'phrase'. Look at the following. I also noticed two "dots" taking the place of spaces in the Where clauses. They were between "OrderDate" nad "Between" As:

OrderDate.Between

Look at the following. It might be right this time.



SELECT [OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM [UnionQuery1]
WHERE (OrderDate Between [Enter begin date:] And [Enter end date:]

UNION SELECT [OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM [UnionQuery2]
WHERE (OrderDate Between [Enter begin date:] And [Enter end date:]);


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MelF]

MICHAEL - YOU'RE A LIFESAVER!!!!!!!!!!!!!!!!! Yes, it worked!! (I used your code, adding the end parentheses to the end of the line.) I was looking and looking on the internet for an example of a where clause prompting for dates within a union query and COULD NOT find one!

THANKS SO MUCH!!!!!!!!!!!!!!

-Melody