Concatenation

[TTThio]

Hi guys,

does anybody know how to concatenate the following:

i = 1

SQL = "SELECT field FROM table WHERE field = '0" & i &"'"

It does result in string :
SELECT field FROM table WHERE field = '01'

but then in setting the recordset, I got error:
"invalid relational operator"

Help please......

 

[Ed2020]

"SELECT field FROM table WHERE field =" & """" & "0" & i & """"

 

[TTThio]

Still get the same message.

 

[TTThio]

Okay, I'm taking the data from the oracle, and here's the complete syntax:

vSQL = "SELECT Count(Account) AS TotalCount FROM TB_OPTY_V Where (Sales_Team = '" & iName(i) & "') AND
(mid(Sales_Stage,1,1)= '" & i & "')"

If I dismiss the second criteria, it works fine. So it must be the 2nd criteria is wrong.

Any idea?

 

[THoey]

Is the Sales_Stage field numeric or text? Terry M. Hoey

 

[TTThio]

Well, I think the problem is the SQL language for Oracle is not the same as for Access. So, I just substitute the 'Mid' with 'SUBSTR' and it works.