Passing parameter into a unioned qry

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Passing parameter into a unioned qry

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-06-28 : 11:47:23

I have this saved query (called 'qryNEW') which gives me a Unioned list from various tables limited to User access in another table -

SELECT [Id],[Date], [Encounter] ,'Complaint' AS IncidentType, [Location] FROM tblComplaints WHERE [Status]= 1 AND tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION SELECT [Id],[Date], [Encounter] ,'Fall' AS IncidentType, [Location] FROM tblFalls WHERE [Status]= 1 AND tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
UNION SELECT [Id],[Date], [Encounter] ,'Event' AS IncidentType, [Location] FROM tblMedicationEvents WHERE [Status]= 1 AND tblEvents.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Event');

If I do this:
Select * from qryNew;
I get a prompt for 'gUserID' - which I enter and get a fine result.

BUt HOw do i pass that id into the initial select statement and avoid the prompt?

Select * from qryNew WHere [gUserID] = 31; - won't work because I am passing the parameters into the unions... you can see.

Seems like I should be able to say

SET [gUserID] = 31
Select * from qryNew;

or

Select * from qryNew; [gUserID] = 31;

or something but I can't figure out the correct syntax.

Can anyone help! Thanks a lot!

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-06-29 : 11:39:29

I believe the correct to call this is something like:

select * from qryNew2, '31'; - where 31 is a value for the decalred parameter in the stored query.

I know that in ASP you can add declared parameters like this:
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.QRY_NEW 31,MS3,2005

why can't i do it in the query manager or from a form?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources