Chr() function doing weird thing!

  • Thread starter Thread starter Southern at Heart
  • Start date Start date
S

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
 
Southern at Heart said:
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
Click to expand...


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);"
 
GREAT!
That's a huge help!

Dirk Goldgar said:
Southern at Heart said:
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
Click to expand...


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)
Click to expand...
 
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...
 
Southern at Heart said:
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...
Click to expand...


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);"
 
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.


Dirk Goldgar said:
Southern at Heart said:
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...
Click to expand...


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)
Click to expand...
 
Sorry, my mistake, this has fixed the problem.
thanks again.


Southern at Heart said:
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.


Dirk Goldgar said:
Southern at Heart said:
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...
Click to expand...


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)
Click to expand...
Click to expand...
 
Back
Top