Chr() function doing weird thing!

[Southern at Heart]

The below code removes any odd chrs from my data. What is STRANGE though is
that it also removes any instance of the letters 'th'
I'd type them in one of my note records again as TH, Th, th
and they'd all get removed.
If I remove the below lines of code, this doesn't happen, but why DOES it
happen, and how can I fix it?

For ichr = 128 To 255
strSQL = "UPDATE tblContacts SET tblContacts.Notes =
Replace([Notes],Chr(" & ichr & "),"""");"
Debug.Print ichr
DoCmd.RunSQL (strSQL)
Next ichr

 

[Dirk Goldgar]

The below code removes any odd chrs from my data. What is STRANGE though
is
that it also removes any instance of the letters 'th'
I'd type them in one of my note records again as TH, Th, th
and they'd all get removed.
If I remove the below lines of code, this doesn't happen, but why DOES it
happen, and how can I fix it?

For ichr = 128 To 255
strSQL = "UPDATE tblContacts SET tblContacts.Notes =
Replace([Notes],Chr(" & ichr & "),"""");"
Debug.Print ichr
DoCmd.RunSQL (strSQL)
Next ichr

It's not the Chr() function doing this, it's the Replace() function. It
happens because -- depending on your system's language settings -- in
comparing text, certain characters are considered to be equal to others. It
appears that characters 222 (Þ) and 254 (þ), which both look like the
"thorn" character to me (pronounced the same as "th" in "thin") are
interpreted as being the having the text value as the "TH" combination. For
example, in the Immediate Window:

?Chr(222) = "TH"
True
?Chr(254) = "TH"
True

You can force a binary comparison by specifying 0 (vbBinaryCompare) for the
"compare" argument of the Replace function:

strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", , , 0);"

 

[Southern at Heart]

GREAT!
That's a huge help!

The below code removes any odd chrs from my data. What is STRANGE though
is
that it also removes any instance of the letters 'th'
I'd type them in one of my note records again as TH, Th, th
and they'd all get removed.
If I remove the below lines of code, this doesn't happen, but why DOES it
happen, and how can I fix it?

For ichr = 128 To 255
strSQL = "UPDATE tblContacts SET tblContacts.Notes =
Replace([Notes],Chr(" & ichr & "),"""");"
Debug.Print ichr
DoCmd.RunSQL (strSQL)
Next ichr

It's not the Chr() function doing this, it's the Replace() function. It
happens because -- depending on your system's language settings -- in
comparing text, certain characters are considered to be equal to others. It
appears that characters 222 (Þ) and 254 (þ), which both look like the
"thorn" character to me (pronounced the same as "th" in "thin") are
interpreted as being the having the text value as the "TH" combination. For
example, in the Immediate Window:

?Chr(222) = "TH"
True
?Chr(254) = "TH"
True

You can force a binary comparison by specifying 0 (vbBinaryCompare) for the
"compare" argument of the Replace function:

strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", , , 0);"


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Southern at Heart]

Something must not be quite right with this. It gives me an error when it
comes to the DoCmd line right after the strSQL= ... line
It says runtime error 3075. (missing operator) in query expression.

I can't figure out the syntax error...

 

[Dirk Goldgar]

Something must not be quite right with this. It gives me an error when it
comes to the DoCmd line right after the strSQL= ... line
It says runtime error 3075. (missing operator) in query expression.

I can't figure out the syntax error...

Hmm. I think it must not like the empty arguments of the Replace function.
Try this instead:

strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", 1, -1, 0);"

 

[Southern at Heart]

Nope. I tried this:
strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", 1, -1, 0);"

....and it gives me the error, something like wrong number of arguments.

I'm running Windows XP Pro, MS Office 2003 if that helps.

Something must not be quite right with this. It gives me an error when it
comes to the DoCmd line right after the strSQL= ... line
It says runtime error 3075. (missing operator) in query expression.

I can't figure out the syntax error...

Hmm. I think it must not like the empty arguments of the Replace function.
Try this instead:

strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", 1, -1, 0);"


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Southern at Heart]

Sorry, my mistake, this has fixed the problem.
thanks again.

Nope. I tried this:
strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", 1, -1, 0);"

...and it gives me the error, something like wrong number of arguments.

I'm running Windows XP Pro, MS Office 2003 if that helps.

Something must not be quite right with this. It gives me an error when it
comes to the DoCmd line right after the strSQL= ... line
It says runtime error 3075. (missing operator) in query expression.

I can't figure out the syntax error...

Hmm. I think it must not like the empty arguments of the Replace function.
Try this instead:

strSQL = _
"UPDATE tblContacts SET tblContacts.Notes = " & _
"Replace([Notes],Chr(" & ichr & "),"""", 1, -1, 0);"


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)