Replace

crmpicco · May 4th, 2005, 04:14 AM

I have four functions:

Code:

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Ampersand character with 'AND'
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_ampersand(ByVal strValue As String) As String
fix_ampersand = Replace(strValue, "&", "AND")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Apostrophe character with "" (empty)
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_apostrophe(ByVal strValue As String) As String
fix_apostrophe = Replace(strValue, "'", "")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Colon character with "" (empty)
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_colon(ByVal strValue As String) As String
fix_colon = Replace(strValue, ":", "")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Comma character with "" (empty)
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function fix_comma(ByVal strValue As String) As String
fix_comma = Replace(strValue, ",", "")
End Function

how can i add them all into one function and just replace each character i.e. comma, apostrophe, etc with "" (blank).

is there a 'strip' command?

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

Santhi · May 4th, 2005, 04:36 AM

Put the three replace statements inside one function.This will replace with ""
Public Function fix_characters(ByVal strValue As String) As String
  fix_apostrophe = Replace(strValue, "'", "")
  fix_colon = Replace(strValue, ":", "")
  fix_comma = Replace(strValue, ",", "")
End Function

crmpicco · May 4th, 2005, 04:52 AM

with that code i get a Compile Error:

Function call on left side of assignment must return Variant or Object

heres my code:

Code:

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Ampersand character with 'AND'
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_ampersand(ByVal strValue As String) As String
fix_ampersand = Replace(strValue, "&", "AND")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Apostrophe character with 'AND'
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_apostrophe(ByVal strValue As String) As String
fix_apostrophe = Replace(strValue, "'", "")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Colon character with ''
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_colon(ByVal strValue As String) As String
fix_colon = Replace(strValue, ":", "")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace Comma character with ''
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_comma(ByVal strValue As String) As String
fix_comma = Replace(strValue, ",", "")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace all characters with ""
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_characters(ByVal strValue As String) As String
  fix_apostrophe = Replace(strValue, "'", "")
  fix_colon = Replace(strValue, ":", "")
  fix_comma = Replace(strValue, ",", "")
End Function

and where i call it:

Code:

fix_characters(Trim(the_Content))

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

crmpicco · May 4th, 2005, 04:55 AM

change to:

Code:

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace all characters with ""
'''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function fix_characters(ByVal strValue As String) As String
  fix_characters = Replace(strValue, "'", "")
  fix_characters = Replace(strValue, ":", "")
  fix_characters = Replace(strValue, ",", "")
End Function

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

Santhi · May 4th, 2005, 05:07 AM

Cool.

crmpicco · May 4th, 2005, 06:14 AM

This should be simple, but it's got me: how do you use 'Replace' to
replace double quotes? This won't work:

Replace(strString,""","'")

to replace the double quotes with singles, for instance. I just know I'm
missing something obvious here.

Thanks.

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

r2d2 · May 4th, 2005, 07:43 AM

Replace(strSting,CHR(34),"'")
Say Hi to Paul & David for me.

gcianfanelli · May 4th, 2005, 07:55 AM

When you say "this won't work", you might want to be a little bit more specific

In fact, if you're using the VB editor, your example

Code:

Replace(strString,""","'")

won't even compile and the editor will highlight it straight away with a warning dialog

Basically you need to escape the double-quote character [Chr$(34)] as it's a special character for the VB compiler

You need to type

Code:

Replace(strString,"""","'")

ie. 4 instances of the double-quote character

crmpicco · May 17th, 2005, 09:35 AM

Is this correct VB code:

Code:

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Replace all problematic characters with ""
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function fix_characters(ByVal strValue As String) As String
    fix_characters = Replace(strValue, "'", "")
    fix_characters = Replace(strValue, ":", "")
    fix_characters = Replace(strValue, ",", "")
    fix_characters = Replace(strValue, """", "")
End Function

I'm not sure if its working....

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

gbianchi · May 17th, 2005, 09:45 AM

mmm you have to replace that chars with nothing or with "" (double quote)...

also.. you will only replace the """" b/c you are using always the same destination...

do something like thisÂ¨

Code:

Public Function fix_characters(ByVal strValue As String) As String
[TAB]dim sStrTemp as string
    sStrTemp = Replace(strValue, "'", "")
    sStrTemp = Replace(sStrTemp , ":", "")
    sStrTemp = Replace(sStrTemp , ",", "")
    sStrTemp = Replace(sStrTemp , """", "")
fix_characters = sStrTemp 
End Function

HTH

Gonzalo