display brackets 1

[hsingh1981]

Hi all i have this sql code (access 2003), am trying to convert it to sql 2000 compatible.

my output of the sql will look like this for one of the columns eg.

(1) Apologies [0]

Am having problem including brackets "(" and "[" to be displayed. I know how to do it in access 2003 and the code for this is :

        sql = "SELECT T_Agenda.AgendaID AS ID, '(' & [Seq1] & ') ' & [R_Agenda1].[Description] & ' [' & [Seq2] & '] ' & [R_Agenda2].[Description] AS AgendaItem "
        sql = sql + "FROM (T_Agenda INNER JOIN R_Agenda1 ON T_Agenda.Agenda1ID = R_Agenda1.Agenda1ID) LEFT JOIN R_Agenda2 ON T_Agenda.Agenda2ID = R_Agenda2.Agenda2ID "
        sql = sql + "WHERE (((T_Agenda.EventID)=" & meetingeventid & ")) "
        sql = sql + "ORDER BY T_Agenda.Seq1, T_Agenda.Seq2;"

how do i do this in sql 2000?


many thanks

 

[r937]

SELECT T_Agenda.AgendaID AS ID
     , '(' + T_Agenda.Seq1 + ') ' + 
       R_Agenda1.Description + 
       ' [' + T_Agenda.Seq2 + '] ' + 
       R_Agenda2.Description AS AgendaItem
  FROM T_Agenda 
INNER 
  JOIN R_Agenda1 
    ON R_Agenda1.Agenda1ID = T_Agenda.Agenda1ID 
LEFT OUTER
  JOIN R_Agenda2 
    ON R_Agenda2.Agenda2ID = T_Agenda.Agenda2ID
 WHERE T_Agenda.EventID = [i]meetingeventid [/i]
ORDER 
    BY T_Agenda.Seq1
     , T_Agenda.Seq2

r937.com | rudy.ca

 

[hsingh1981]

Hi thankyou for replying but i still get the same error msg.

which is

"MESSAGE: Syntax error converting the varchar value '(' to a column of data type int."

any other suggestions?

 

[r937]

change T_Agenda.Seq1 to CAST(T_Agenda.Seq1 AS VARCHAR)

similarly for Seq2

r937.com | rudy.ca

 

[hsingh1981]

BRILLIANT....just the way i wanted it.....