Fixing the & sign

[Southern at Heart]

strSQL = "UPDATE tblContacts SET tblContacts.HomeAddressStreet =
Replace([HomeAddressStreet],""&"",""&"") " & _
"WHERE (((tblContacts.HomeAddressStreet) Like ""*[&]*""));"
DoCmd.RunSQL (strSQL)

Fixing the & sign

I have the above code that replaces the '&' symbol with '&'
Is there a way to have it check each '&' symbol to see if it is already
followed by 'amp;' and not change it if that's the case? My '&' signs must
all be in the form '&' but if some of them are already fixed that way, then
my code above will mess it up by turning '&' into '&'
thanks

 

[Southern at Heart]

ps. Is there an easier way to turn this & into &
in every field in the table (I have about 12 fields)
thanks.

 

[Dirk Goldgar]

strSQL = "UPDATE tblContacts SET tblContacts.HomeAddressStreet =
Replace([HomeAddressStreet],""&"",""&"") " & _
"WHERE (((tblContacts.HomeAddressStreet) Like ""*[&]*""));"
DoCmd.RunSQL (strSQL)

Fixing the & sign

I have the above code that replaces the '&' symbol with '&'
Is there a way to have it check each '&' symbol to see if it is already
followed by 'amp;' and not change it if that's the case? My '&' signs
must
all be in the form '&' but if some of them are already fixed that way,
then
my code above will mess it up by turning '&' into '&'

In my newreader, at least, your message didn't come out making sense,
because (I think) your typed literals "& a m p ;" (without the spaces in
between) appears after posting as a simple "&".

Are you trying to convert the simple character "&" to the HTML code "& a m p
;" (without the spaces)? And you don't want to convert "& a m p ;" into "&
a m p ; a m p ;"? If so, I think I would write a function that receives a
string and returns a copy of the string in which the replacement has been
done. Something like:

'------ start of code ------
Function FixAmpersand(InputString) As String

Dim strOutput As String
Dim lngPos As Long

strOutput = InputString

Do
lngPos = lngPos + 1
lngPos = InStr(lngPos, strOutput, "&")
If lngPos > 0 Then
If Mid$(strOutput, lngPos + 1, 4) <> "amp;" Then
strOutput = _
Left(strOutput, lngPos) & _
"amp;" & _
Mid$(strOutput, lngPos + 1)
lngPos = lngPos + 4
End If
End If
Loop While lngPos > 0

FixAmpersand = strOutput

End Function
'------ end of code ------

Then I'd call the function from the update query:

strSQL = _
"UPDATE tblContacts " & _
"SET HomeAddressStreet = FixAmpersand(HomeAddressStreet) " & _
"WHERE HomeAddressStreet Like ""*[&]*""";

Of course, I may have totally misunderstood what you're trying to do.

My personal inclination in cases like this is to save the text without
web-oriented transformations -- leaving the ampersands as ampersands -- and
make those transformations only when I'm about to render the text for the
web page.

 

[vanderghast]

As rendered in my ng reader, the request does not make sense:

replaces the '&' symbol with '&'


but as I imagine the original question, let say, to replace "a" to "ae" ,
but not where it is already "ae", ie, not to make it "aee" ?

Then, change the "ae" to, say "a", then the "a" to "ae" :


UPDATE tblContacts SET homeAddressStreetReplace(
Replace(Replace(homeAddressStreet, "ae", "a"), "a", "ae")



Vanderghast, Access MVP