SQL String problem

[PTinVT]

Hi, I'm trying to change fields in my table that have back slashes so that the backslash and everything after it are deleted. Ex. Coronary Artery Disease/Hypertension/Treatment would be changed to simply Coronary Artery Disease.
I have used the following code successfully in a little SQL Tester program, but when I put it in my VB application, it returns a type mismatch error (runtime 13).
I am using Microsoft Jet 4.0. Any advice? Thanks.

"UPDATE Mesh Set Meshterm = LEFT(Meshterm, InStr(Meshterm, " / &quot-1)WHERE Instr(meshterm, " / &quot>0"

 

[CajunCenturion]

Within a VB program, given the syntax in the statement as shown, is attempting to perform a double division operation.

"UPDATE Mesh Set Meshterm = LEFT(Meshterm, InStr(Meshterm, "
------------------------------------  (/)
")-1)WHERE Instr(meshterm, "
------------------------------------  (/)
")>0"

where the quotes are separating the terms of the division.

You might want to try building the statement using the Chr(34) function, which evaluates to a double quote

SQLStmt = "UPDATE Mesh Set Meshterm = LEFT(Meshterm, InStr(Meshterm, " & Chr(34) & " / " & Chr(34) & ") - 1) WHERE Instr(meshterm, " & Chr(34) & " / " & Chr(34) & ") > 0"

This will concatenate several strings together, with the quotes becomming embedded in the resultant statement. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PTinVT]

Hi, your reply was a great help. I don't get an error message anymore; however, fields like this (Acrylic Resins/adverse effects/*therapeutic use) still remain in the table?! I am having a hard time wrapping my brain around this. How can I get it to change the field to simply Acrylic Resins?

 

[CajunCenturion]

You might want to remove the spaces on either side of the slashes.

SQLStmt = "UPDATE Mesh Set Meshterm = LEFT(Meshterm, InStr(Meshterm, " & Chr(34) & "/" & Chr(34) & &quot - 1) WHERE Instr(meshterm, " & Chr(34) & "/" & Chr(34) & &quot > 0" Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PTinVT]

Oh, that's beautiful! It works great. Thanks! :-D