sql string, +'/'+convert(char(3)..)

[doristsr]

HI;

I m writing excel vba, i would like to execute a SQL query :

select * from receiver_line
where purc_order_id+'/'+cast(purc_order_line_no as varchar(3))
+'/'+receiver_id+'/'+cast(line_no as varchar(3))
not in( select purc_order_id+'/'+cast(purc_order_line_no as
varchar(3))
+'/'+receiver_id+'/'+cast(receiver_line_no as varchar(3))
from payable_line where receiver_id is not null )

may i know how do i write it in vba?

can it be like the following?

strSql = "select * from receiver_line" & _
"where purc_order_id+'/'+convert(char(3),
purc_order_line_no)+'/'+receiver_id+'/'+convert(char(3),line_no)" & _
"not in( select
purc_order_id+'/'+convert(char(3),purc_order_line_no)+'/'+receiver_id+'/'+convert(char(3),receiver_line_no)"
& _
"from payable_line where receiver_id is not null )"

i keep on getting error, can anyone correct me? thanks for your great
help

Doris

 

[Tim Williams]

Doris,

Make sure you add spaces to separate consecutive parts of your SQL

strSql = "select * from receiver_line" & _
"where purc_order_id+'/'+convert(char(3),

gives you
select * from receiver_linewhere purc_order_id+'/'+convert(char(3),...

you need a space before "where"

Tim

 

[doristsr]

thks... it work now

 

[Andrew Taylor]

strSQL has no space between "receiver_line" in the first line of
code and "where" in the second, so it begins
select * from receiver_linewhere ....

So make the first line say
strSql = "select * from receiver_line " & _

BTW you can debug this sort of problem by stepping through
the code and lookin at the value of strSQL in the Immediate
window.

Hope this helps
Andrew