• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

VB COde: replace string without using replace function

Hi all,

i need to do some on the fly replacing of text but the characters i need replacing wont work with the standard replace function.

i need to replace " with ' in my strings, is there any code that will enable me to do this?

cheers
0
Dubs
Asked:
Dubs
  • 12
  • 5
  • 5
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
replace(yourvariable , """", "'" )
0
 
DubsAuthor Commented:
i tried that originally but it doesn't work- also it's only one " that i need to replace with one '..

so when i write : replace(yourvariable , """, "'" ) the code after the ' goes green and is treated as a comment.

this:
replace ([Reason for Communication:], """", "'") prompts the compile error: expected =

and this:

replace [Reason for Communication:], """", "'" prompts run time error '94': invalid use of null

any ideas?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this looks like you are using this in Access Query?

not that """"  is a string with 1 " in it...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
DubsAuthor Commented:
yeah thats right's it's a function on an adp...

what i need it to do is find any " marks and replace it with a ' mark

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in a ADP? so you have a sql server database below ?

then it might work like this:

replace ([Reason for Communication:], '"', '''')
0
 
cquinnCommented:
Use the Chr values in your replace statement - Double quote is Chr(34), single quote is Chr(39)

Replace(yourvariable , Chr(34),Chr(39) )
0
 
DubsAuthor Commented:
that would work- but says invalid use of null?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have null values in the field, you will have to handle that apart:


case when [Reason for Communication:] is null then null else replace ([Reason for Communication:], '"', '''') end
0
 
DubsAuthor Commented:
its really bizarre as the field isn't null,

ill type in something with " marks and get the invalid use of null error so i go into the code and look at the value of the field in the immediate window and it will say null depsite there having " marks in it...

so not sure some null handling code will do it as at the moment it's detecting text and " marks as null.

greg
0
 
JR2003Commented:
Have you tried adding an empty string to the value before is it parsed?
e.g.

Raplca(myVar & "", "with","")
0
 
DubsAuthor Commented:
still get the invalid use of null message-

if i try putting the code in brackets like you suggest i get an 'expected = '  error message

greg

0
 
JR2003Commented:
Can you paste the code in this thread?
0
 
DubsAuthor Commented:
ive tried these:


Replace [Reason for Communication:], Chr(34), Chr(39)
>>>>>>>>>>>>>

Replace ([Reason for Communication:], Chr(34), Chr(39))

>>>>>>>>>>>>>

Dim myVar As Variant
myVar = [Reason for Communication:]

Replace myVar, Chr(34), Chr(39)
>>>>>>>>>>>>>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>yeah thats right's it's a function on an adp...
in a SQL Server stored function? or in a Access Macro VBA function?

in VBA:
Dim myVar As Variant
myVar = [Reason for Communication:]
if not IsNull(myVar) then
  myVar = Replace (myVar, Chr(34), Chr(39))
end if
0
 
DubsAuthor Commented:
sorry i didnt make that clear-  its an adp but using access vba

for the record the code you just posted didnt work- i can see what its doing and would have done something similar but it doesn't throw up an error messages or replace the " with '

0
 
JR2003Commented:
Why aren't you using strings? i.e.

Dim myStr As String
myStr = "[Reason for Communication:]"

Replace myStr, Chr(34), Chr(39)
0
 
DubsAuthor Commented:
Why aren't you using strings? i.e.

>>>>>>

because im still getting invalid use of null when i tried it that way

greg
0
 
DubsAuthor Commented:
Private Sub Pastoral_Concern_AfterUpdate()

If IsNull([Pastoral Concern]) = False Then
Replace ([Pastoral Concern]), Chr(34), Chr(39)
Else
End If


End Sub

this is what im using, it's simply not working- it lets me move on to the next field with no replaces taking place- im losing the plot now- i don't understand how or why this wouldn't work as ivce used similar code before which has worked fine...

ive bumped the points as i relly need an answer

cheers

greg
0
 
JR2003Commented:
You could loop thourgh each character:

Public Function ReplaceDoubleWithSingleQuotes(ByVal sIn As String) As String
   
    Dim sTmp As String
    Dim i As Long
    Dim j As Long
   
    j = 1   ' start looking from 1st character
    Do
        i = InStr(j, sIn, """")                    ' look for a double quote
        If Not i > 0 Then
            Exit Do                 ' no quotes found, break out of loop
        End If
        sTmp = sTmp + Mid(sIn, j, i - j) & "'"   ' add section to result string and replace with a single quote
        j = i + 1                                 ' move on to next character
    Loop
    ReplaceDoubleWithSingleQuotes = sTmp + Mid(sIn, j)                    ' add on remainder of string to result string
   

End Function
0
 
DubsAuthor Commented:
Hi,

i would try that but really what im after is why the replace function isn't working?

it works in other instances but wont work for this..

greg
0
 
DubsAuthor Commented:
hi,

that works but it doesn't do what i need - i need to replace " with this '

i tried amending the code:

from- i = InStr(j, sIn, """")                    ' look for a double quote

to-  i = InStr(j, sIn, """)                    ' look for a double quote

but im gettimng syntax errors now-

greg
0
 
DubsAuthor Commented:
Hi all,

ive split the points- i didn't use any of the answers  but the closest one was the one that used the (yourvariable , Chr(34),Chr(39) )

as that's what ive used as my answer but had to change the syntax.  

the other solutions would work though so they've got points for effort

cheers
greg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now