Solved

VB COde: replace string without using replace function

Posted on 2006-11-08
23
241 Views
Last Modified: 2010-04-30
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
Comment
Question by:Dubs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 5
  • +1
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17896419
replace(yourvariable , """", "'" )
0
 

Author Comment

by:Dubs
ID: 17896446
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17896485
this looks like you are using this in Access Query?

not that """"  is a string with 1 " in it...
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:Dubs
ID: 17896513
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17896577
in a ADP? so you have a sql server database below ?

then it might work like this:

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

Accepted Solution

by:
cquinn earned 20 total points
ID: 17896809
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
 

Author Comment

by:Dubs
ID: 17896881
that would work- but says invalid use of null?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17896928
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
 
LVL 18

Expert Comment

by:JR2003
ID: 17899382
0
 

Author Comment

by:Dubs
ID: 17928910
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
 
LVL 18

Expert Comment

by:JR2003
ID: 17929096
Have you tried adding an empty string to the value before is it parsed?
e.g.

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

Author Comment

by:Dubs
ID: 17929117
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
 
LVL 18

Expert Comment

by:JR2003
ID: 17929140
Can you paste the code in this thread?
0
 

Author Comment

by:Dubs
ID: 17929157
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 20 total points
ID: 17929574
>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
 

Author Comment

by:Dubs
ID: 17929754
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
 
LVL 18

Expert Comment

by:JR2003
ID: 17930167
Why aren't you using strings? i.e.

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

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

Author Comment

by:Dubs
ID: 17978775
Why aren't you using strings? i.e.

>>>>>>

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

greg
0
 

Author Comment

by:Dubs
ID: 17978954
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
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 40 total points
ID: 17979100
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
 

Author Comment

by:Dubs
ID: 17979124
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
 

Author Comment

by:Dubs
ID: 17979544
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
 

Author Comment

by:Dubs
ID: 17979664
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question