VB COde: replace string without using replace function

replace(yourvariable , """", "'" )

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?

this looks like you are using this in Access Query?

not that """"  is a string with 1 " in it...

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

in a ADP? so you have a sql server database below ?

then it might work like this:

replace ([Reason for Communication:], '"', '''')

that would work- but says invalid use of null?

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

http://www.devx.com/vb2themax/Tip/18933

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

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

Raplca(myVar & "", "with","")

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

Can you paste the code in this thread?

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)
>>>>>>>>>>>>>

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 '

Why aren't you using strings? i.e.

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

Replace myStr, Chr(34), Chr(39)

Why aren't you using strings? i.e.

>>>>>>

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

greg

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

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

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

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