Search the Community
Showing results for tags 'apostrophe'.
Hello, I am trying to do a function to remove and replace some special characters from a text field using formula DataType, However, how can I remove the apostrophes (') Here is the formula (Last one with apostrophe) CASE WHEN Charindex('!',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], '!', '') WHEN Charindex('#',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], '#', '') WHEN Charindex(':',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], ':', '') WHEN Charindex(''',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], ''', '') END
I am posting this so it can help someone else if it comes up. I'm using the SOAP protocol in Excel 2003 (VBA) and I want to update a record. The field DataType I'm updating is the Text(64,000) (64, character text box). The basic Visual Basic code looks like: ---------- dim ValueList as String ValueList = "'This is the text that will be inserted into the specified field in FieldList.'" lAffected = aobjWS.UpdateData(strAccountID, strProfileName, strPassword, ObjectName, IsView, FieldList, ValueList, Criteria) ---------- ValueList is a string, and it must be inside double-quotes for VB sake to identify what ValueList will be, and the single quotes or apostrophes are the delimiters that Caspio uses to identify the string that will go into a field. The problem occurs if you want the following to be put in a field: ValueList = "'I can't put this sentence in!'" The middle single quote/apostrophe (') in the word CAN'T is being treated as a delimiter or something such that Caspio doesn't know how to put the data in the field. The solution is to use two single quotes/apostrophes wherever one would ordinarily be: ValueList = "'It''s now possible to put this sentence in correctly!'" This puts the following into the desired database field: It's now possible to put this sentence in correctly! In VB, here is the code I ultimately used to replace all single quotes with two single quotes: (you may need to paste into Word or VBA or something to see the double vs. single quotes). Also worth noting, Before using the "Replace" function I chose to remove the first and last single quotes since they need to remain as single quotes. I put them back on in the final line of code: ---------------- dim NewString as String NewString = "'I'd love contractions, but I don't always use 'em properly'" 'the next two lines take out the first and last single quotes NewString = Right(NewString, Len(NewString) - 1) NewString= Left(NewString, Len(NewString) - 1) ValueList = "'" & Replace(NewString, "'", "''") & "'" ---------------- Now "ValueList" is ready to be used in: lAffected = aobjWS.UpdateData(strAccountID, strProfileName, strPassword, ObjectName, IsView, FieldList, ValueList, Criteria) Caspio reference: http://static.caspio.com/pdfs/Caspio-Bridge-Web-Service-API.pdf%C2'> Happy coding!