Solved

VB COde: replace string without using replace function

Posted on 2006-11-08
23
226 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
  • 12
  • 5
  • 5
  • +1
23 Comments
 
LVL 142

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 142

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
 

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 142

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 142

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 142

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now