Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

String in Query

Status
Not open for further replies.

pingit

MIS
Nov 24, 2003
37
NZ
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
 
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.
 
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
 

Did you try:
Code:
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
 

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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top