Folllowing on from a previous post, i have created a stored query as follows.

SELECT
    c.ID, c.Company_Name, p.[level], 1 As QueryNbr
FROM
    (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
LEFT JOIN
    Package AS p ON s.Package_ID = p.ID
WHERE
    c.Category = 'EXH'
AND
    (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
AND
    EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID =
@EventID AND Company_ID = c.ID)
AND
    (INT(Start_Date) <= @StartDate) AND (INT(End_Date) >=  @EndDate)
ORDER BY
    p.[level] DESC , c.Company_Name, c.ID
UNION
    SELECT
        c.ID, c.Company_Name, p.[level], 2 As QueryNbr
    FROM
        (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
    LEFT JOIN
        Package AS p ON s.Package_ID = p.ID  
    WHERE
        c.Category = 'EXH'
    AND
        (s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
    AND
        EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID
=@EventID AND Company_ID = c.ID)
    ORDER BY
        c.Company_Name, c.ID

I want use the results from the preceding query in the following way:

SELECT
    ID, Company_Name, level, QueryNbr
FROM
    ExhibitorsSearchByName 
    //this query requires three parameters, Start_Date, End_Date, Event_ID
GROUP BY
    ID, First(Company_Name), First(level), First(QueryNbr)
WHERE
    Company_Name LIKE '%myCriteria'

What would be the best way to execute the previous query in ASP, including
sending the appropriate parameters???

Sample code would be great!

Cheers,
Adam

AJ wrote:
Is this SQL Server or Access? ... OK, I just found your previous post and
discovered that this is Access.

I'm a little surprised it's allowing the @ symbol in your parameter names
.... I'm assuming you tested this and it works correctly in Access.

<snip>
Did you try running the following query? It should not have worked as
written.

The '%myCriteria should also be a parameter.
The WHERE clause (which should really come _before_ the GROUP BY clause if
you were going to keep using the WHERE clause) needs to be a HAVING clause,
because you are filtering by the result of an aggregate function that
provides a result _after_ the grouping is done.
The GROUP BY is strange. I think you want the aggregates in the Select
statement, don't you? Like this:
SELECT
    ID,
    First(Company_Name) As Company_Name,
    First(level) As level,
    First(QueryNbr) As QueryNbr
FROM
    ExhibitorsSearchByName
GROUP BY
    ID
HAVING
    First(Company_Name) LIKE [@myCriteria]


Test this query in Access before attempting to run it in ASP!!!

I would save this query as well - call it "ExhibitorsForSpecifiedCompany".
When you test it, take note of the order in which Access prompts you for
parameter values. You will need to supply the values in the same order when
executing it from ASP.
This is air code so it's untested:

Dim cn, rs, sDate, eDate, Event_ID, criteria
sDate=#2006-7-1#
eDate=#2006-7-31#
Event_ID = 28
criteria="%criteria"

set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=p:\ath\to\db.mdb"
set rs=createobject("adodb.recordset")

'This is my guess as to the parameter order. Modify if your testing
'shows the order is different:

cn.ExhibitorsForSpecifiedCompany Event_ID,sDate, _
eDate, criteria, rs

if not rs.eof then ...

Bob Barrows

regional settings inconsistency
Embed image in CDOSYS message
ASP Redirects
Variable Scope and Functions
File uploading mixed with form input