Parameters In a Union Query

[forey]

Hi All,

I'm trying to find the best way to accomplish the following:

I have a union query in an Access XP database (pasted below)

SELECT Contacts.Company,Contacts.dba, Contacts.Misc1, Contacts.Misc2,
Contacts.Address1, Contacts.City, Contacts.State, Contacts.Zip1,
Contacts.top_50, Contacts.B_ID as BID,'BCM' as thesource
FROM Contacts
UNION ALL
SELECT BrokerList.BrokerName AS company,BETdba as dba, BrokerList.BDM
AS misc1, BrokerList.Status AS misc2, BrokerList.Address AS address1,
BrokerList.City, BrokerList.State, BrokerList.Zip AS zip1, False AS
top_50, BrokerList.BrokerID as BID, 'BL' as thesource FROM BrokerList
UNION ALL
SELECT Ineligible.Broker AS Company,'NA' as dba, 'NA' AS Misc1,
Ineligible.Status AS Misc2, Ineligible.Address AS Address1,
Ineligible.City, Ineligible.State, Ineligible.Zip AS Zip1, False AS
top_50, 'NA' AS BID, 'IL' AS the_source
FROM Ineligible

I'm using an ASP page to display the data. Currently I'm using a
parameter after the entire union query has executed - very inefficient

Using ADO what is the best way to pass the parameters to the union
query --
1.) Execute a saved query -- if so, how do I declare the
params in a union query in access. Then I would append params using
the command object? Or could I use execute with the Connection object
and pass the params that way?
2.) Use a sql string with the params declared in the asp
page and execute it dynamically.

The same parameters will be used in each individual query, Do I need to
pass them for each query? Any help would be appreciated

Thanks in Advance
Brian

[Rich P]

Hi Brian,

I have used functions inside of union queries. However, I don't know if
this would work from an ASP. But you create a function which returns a
string, int, ... Inside the function you set the value of the function
to a global variable.

Function x() As string
x = globalVar
End Function

You set the value of the global var from a sub that you call from your
client app. This is doable from Excel, another Access app, a vb app.
But I don't know if you can call a sub from an ASP. In the sub you set
the value of the global var.

public globalvar As String

Sub setVal(x as string)
globalvar = x
End Sub

The thing with the function is that a query from the query builder can
have a function in the select statement.

Select ... From tblx where something = x()

Two other alternatives would be 1) use an ADODB command object and set
the select statement in the text of the command object
<%
'--this is off the top of my head - haven't used asp for a while
set cmd = createObject("ADODB.Command") 'I forget the class
cmd.ActiveConnection = filepath(something)
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select... Union All select... Union..."
set RS = cmd.Execute
Do While Not RS.EOF
%>
<table>
<tr><td><%RS(0)%><td><%RS(1)%><td>....
</table>

Option 2) populate one table from the tables that make up the union
query, again, using the command object and an insert statement, then use
a command object to select the data from the table you populated

hth

Rich

*** Sent via Developersdex http://www.developersdex.com ***