Russell,
The ORDER BY and INTO CURSOR clause affect result of union. They should be at the very end of select. Also I would recomend that you pad calculated character fileds so they wouldn't be unexpectedly truncated.
SELECT PADR('***All Sales Reps*** ',50) AS Name, '*****'AS salesrepid ;
FROM salesreps ;
UNION ALL ;
SELECT PADR(ALLTRIM(lastname)+', '+ALLTRIM(firstname),50) AS Name, salesrepid ;
FROM salesreps ;
WHERE salesreps.active = 1 ;
ORDER BY name ;
INTO CURSOR curSalesRep
BTW, I intentionaly reformatted your query to make it readable.
>I have the following SQL statement as the rowsource of a dropdown box that works fine:
>
>SELECT '***All Sales Reps*** ' AS Name, '*****'AS
>salesrepid FROM salesreps UNION SELECT ALLTRIM(lastname)+', '+ALLTRIM
>(firstname) AS Name,salesrepid FROM salesreps INTO CURSOR curSalesRep ORDER BY name
>
>I would like to add a WHERE statement to the second Select statement as follows:
>
>SELECT '***All Sales Reps*** ' AS Name, '*****'AS salesrepid FROM salesreps UNION SELECT ALLTRIM(lastname)+', '+ALLTRIM(firstname) AS Name,salesrepid FROM salesreps INTO CURSOR curSalesRep WHERE salesreps.active = 1 ORDER BY name
>
>However, when I do this I get a syntax error. What am I doing wrong? What is the correct syntax for this statement?
>
>Thanks in advance,
>Russell
--sb--