String in Query

[pingit]

Hello All

I have a query that returns values. Those values are then used in an insert query. The problem seems to be that some of the values returned are strings with "()" included. Im getting 3075 missing operator listing the string as the issue.
Here is the insert query

strSQL = "INSERT INTO StaffCode ( CodeID, ServiceItemCode, ServiceItemDescription, SSSID ) SELECT " + CStr(rst.Fields("CodeID")) + "," + rst.Fields("ServiceItemCode") + "," + rst.Fields("ServiceItemDescription") + "," + CStr(iSSSID) + ";"

What do I do to the ServiceItemDescription to get the string inserted?

Thanks in advance

 

[GlennUK]

So, are you saying that you want"(" and ")" removed from whatever value is returned by rst.Fields("ServiceItemDescription")?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[pingit]

Hello Glenn

Thanks for the reply. I would like to keep all strings with all chars. I just need to know to let VBA know it is a string and for VBA to process it


Cheers V

 

[Andrzejek]

Did you try:

strSQL = "INSERT INTO StaffCode ( CodeID, ServiceItemCode, ServiceItemDescription, SSSID ) SELECT " + CStr(rst.Fields("CodeID")) + "," + rst.Fields("ServiceItemCode") + ", [red]'[/red]" + rst.Fields("ServiceItemDescription") + "[red]'[/red]," + CStr(iSSSID) + ";"

See the RED quotes around ServiceItemDescription

Also, could you do:
[tt]
Debug.Print strSQL
[/tt]
to show the SQL?

Have fun.

---- Andy

 

[SkipVought]

The problem seems to be that some of the values returned are strings with "()" included.

A picture is worth 1K words!

Please post a sample of the values, as they appear in your data. It would be most helpful to COPY a range and PASTE here.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pingit]

Hello All

Thanks for your reply's. I think I have solved the issues. I have added ' to each end of the strings as I build the SQL and it appears to work

Thanks
v