Trying to concatenate variable into SQL Statement

[HTDon]

I have the following working SQL in my Function;

strSQL2 = "SELECT Merch.Loc, Merch.Hub, Merch.Item, Merch.[Total Qty], Merch.[Avg Cost]" & _
"into TblTemp2 " & _
"FROM [Merch]" & _
"WHERE (((Merch.Item)= '1387' ));"


I want to replace 1387 in the last line with a variable called itm, the string below doesn't work, despite trying about 1000 syntax permutations!

strSQL2 = "SELECT Merch.Loc, Merch.Hub, Merch.Item, Merch.[Total Qty], Merch.[Avg Cost]" & _
"into TblTemp2 " & _
"FROM [Merch] WHERE (Merch.Item)" & " = " & itm & ";"

What did I screw up?

Thanks

Don

[MikeTheBike]

Hi

Try this


strSQL2 = "SELECT Merch.Loc, Merch.Hub, Merch.Item, Merch.[Total Qty], Merch.[Avg Cost]" & _
"into TblTemp2 " & _
"FROM [Merch]" & _
"WHERE (((Merch.Item)= '" & itm & "' ));"


ie. just replace 1387 with " & itm & "

but leave to 2 apostrophies in place !!

HTH

MTB

[HTDon]

Thanks from across the big pond!

Worked like a charm, I knew there was one comonation of ' " ] } and )s that I missed!

[otacons]

YEP, that was it!

string variable in sql statement visual basic 6

it works super, thanks a lot! =)

My problem was that I wrote: '& itm & ', cause my string variable already had " ", but still you have to write it on the sql statement.

item = "abcde"

'" & itm & "'

Hi

Try this


strSQL2 = "SELECT Merch.Loc, Merch.Hub, Merch.Item, Merch.[Total Qty], Merch.[Avg Cost]" & _
"into TblTemp2 " & _
"FROM [Merch]" & _
"WHERE (((Merch.Item)= '" & itm & "' ));"


ie. just replace 1387 with " & itm & "

but leave to 2 apostrophies in place !!

HTH

MTB