Data-linked text box has different value than the field in the underlying recordset

[PeteJohnston]

I have a table holding character values that should be used to replace occurences in text fields on another table. These are characters that do not occur in the standard english character set. Examples are ă and ƣ. If I view the table, create a query or display the data on a form then it shows correctly. If I use VBA under the covers in an Openrecordset/MoveNext loop then I get rst.Fields("Actual") value of Ā returning A and ă returning a.

Any clues/solutions to why this is happening?

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MajP]

How are you verifying what is returned? Is it not returning the correct value or is it rendering the returned value incorrectly for display. My guess is the latter. You could verify your values with something like.
debug.print ASC(rst.fields("actual")

 

[PeteJohnston]

I'm stepping thru in debug, checking the value in two ways

With Me.Recordset
    Do While Not .EOF
        sWork = Nz(.Fields("Actual"), "")
        sWork = txtActual
        .MoveNext
    Loop
End With

The form is in continuous mode and this code reads each row in turn. The txtActual field is a text box with DataSource=Actual. Although the form displays Ā the fields in the code show A regardless of whether I look at the field from the recordset or the text box.

For each of the 'invalid' characters I need to do an SQL select with

sSQL = "Select * From tblImport Where F1 Like '*" & [i]SpecialCharacter[/i] & "*'"
Set rst = CurrentDb.OpenRecordset sSQL

to identify all of the import rows where the data may need changed. When I build the string for the OpenRecordset it holds A, not Ā so selects the wrong rows.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[TheAceMan1]

How are ya PeteJohnston . . .

Is VBA using the same [blue]font[/blue] as that set in Access options or possibly that set for the control?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PeteJohnston]

The character value is correct in the places where outside world sees the font.

It is in the VBA debug environment that it seems wrong. The fact that SQL selects the large number of records which contain an A instead of the far fewer number with an Ā means that what I saw is what I got. Would the font have an affect on the character being displayed in the code window? Maybe, but I'm not convinced that the code editor font would change what SQL retrieved from the database table. I'll check it out when I get in to work tomorrow.

I found a web page which mentions changing the font in the code editor.

http://www.worldbestlearningcenter.com/tips/change-font-of-vba-code-edit.htm

I'll check that out tomorrow too.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MajP]

In vbe
Tools-Options-Editor Format-Normal Text

Choose Courier New (Western), and see if you see what you expect. The font will definitely effect how it is rendered. Bot the font in the form and in the VBE may be the same, so the rendering is not as expected.
I think the SQL issue is seperate. The where statement seems odd. Are the special characters stored in the same table as the data you are checking..

 

[PeteJohnston]

The code editor was originally Courier New and I've changed it to Courier New (Central European) but it still shows A instead of the actual value Ā.

I've put you off the scent a little with how I typed the SQL statement. The SpecialCharacter in italics was just intended to show that it held the characters that may be wrong. The actual code is

sSQL = "Select * From tblImport Where F1 Like '*" & rst.Fields("Actual") & "*'"

The SQL issue is not a separate issue since it is being caused by the incorrect character. The incorrect WHERE clause searches for any rows containing A not Ā and therefore finds

Andrew
Alexander
Aiden

instead of

Ārghun
Ā-Kura
Ā ishah


PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[TheAceMan1]

To All . . .

I was able to duplicate the problem quite easily.

[ol][li]Copied the characters "Ă㥹" from [blue]Character Map[/blue] and appended to a name in a table.[/li]
[li]On a form with [blue]recordsource[/blue] of the tablename a button executes the following:

[blue]   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   rst.MoveFirst
   
   Debug.Print rst!qName
   
   Set rst = Nothing[/blue]

[/li][/ol]

Lo and behold I get "David [purple]AaAa[/purple]"

Back to the drawing board . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

As I keep saying you need to check the asc value returned from the recordset to understand if this is a rendering problem or if somehow the value is not returned correctly. I cannot replicate this problem.

I loaded a table with special characters and its ascii value to demonstrate

Public Sub readSpecial()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblSpecialChar")
  On Error Resume Next
  Do While Not rs.EOF
    Debug.Print rs!specialCharacter & " " & (rs!ascvalue) & " " & Asc(rs!specialCharacter)
    rs.MoveNext
  Loop
End Sub

: 58 58
; 59 59
< 60 60
= 61 61
> 62 62
? 63 63
@ 64 64
A 65 65
B 66 66
C 67 67
D 68 68
E 69 69
F 70 70
G 71 71
H 72 72
I 73 73
J 74 74
K 75 75
L 76 76
M 77 77
N 78 78
O 79 79
P 80 80
Q 81 81
R 82 82
S 83 83
T 84 84
U 85 85
V 86 86
W 87 87
X 88 88
Y 89 89
Z 90 90
[ 91 91
\ 92 92
] 93 93
^ 94 94
_ 95 95
` 96 96
a 97 97
b 98 98
c 99 99
d 100 100
e 101 101
f 102 102
g 103 103
h 104 104
i 105 105
j 106 106
k 107 107
l 108 108
m 109 109
n 110 110
o 111 111
p 112 112
q 113 113
r 114 114
s 115 115
t 116 116
u 117 117
v 118 118
w 119 119
x 120 120
y 121 121
z 122 122
{ 123 123
| 124 124
} 125 125
~ 126 126
 127 127
€ 128 128
 129 129
‚ 130 130
ƒ 131 131
„ 132 132
… 133 133
† 134 134
‡ 135 135
ˆ 136 136
‰ 137 137
Š 138 138
‹ 139 139
Π140 140
 141 141
Ž 142 142
 143 143
 144 144
‘ 145 145
’ 146 146
“ 147 147
” 148 148
• 149 149
– 150 150
— 151 151
˜ 152 152
™ 153 153
š 154 154
› 155 155
œ 156 156
 157 157
ž 158 158
Ÿ 159 159
  160 160
¡ 161 161
¢ 162 162
£ 163 163
¤ 164 164
¥ 165 165
¦ 166 166
§ 167 167
¨ 168 168
© 169 169
ª 170 170
« 171 171
¬ 172 172
­ 173 173
® 174 174
¯ 175 175
° 176 176
± 177 177
² 178 178
³ 179 179
´ 180 180
µ 181 181
¶ 182 182
· 183 183
¸ 184 184
¹ 185 185
º 186 186
» 187 187
¼ 188 188
½ 189 189
¾ 190 190
¿ 191 191
À 192 192
Á 193 193
 194 194
à 195 195
Ä 196 196
Å 197 197
Æ 198 198
Ç 199 199
È 200 200
É 201 201
Ê 202 202
Ë 203 203
Ì 204 204
Í 205 205
Î 206 206
Ï 207 207
Ð 208 208
Ñ 209 209
Ò 210 210
Ó 211 211
Ô 212 212
Õ 213 213
Ö 214 214
× 215 215
Ø 216 216
Ù 217 217
Ú 218 218
Û 219 219
Ü 220 220
Ý 221 221
Þ 222 222
ß 223 223
à 224 224
á 225 225
â 226 226
ã 227 227
ä 228 228
å 229 229
æ 230 230
ç 231 231
è 232 232
é 233 233
ê 234 234
ë 235 235
ì 236 236
í 237 237
î 238 238
ï 239 239
ð 240 240
ñ 241 241
ò 242 242
ó 243 243
ô 244 244
õ 245 245
ö 246 246
÷ 247 247
ø 248 248
ù 249 249
ú 250 250
û 251 251
ü 252 252
ý 253 253
þ 254 254
ÿ 255 255
aÀAÀ 999 97

So my test shows that the correct character is rendered and returned. So you need to run the same test. So there appears to be more going on here because a recordset can return the special characters.

 

[PeteJohnston]

MajP
As you can see AceMan has managed to replicate it quite easily.

As part of my load of these special characters I have a column to hold the Ascii representation as well as the character. It is populated using the ASC function. It shows the following

Character Ā Ascii 65
Character ā Ascii 97

As we can see from your table these should be A and a respectively. If I go into Excel and paste the same character into a =Code("Ā") function I get 65 too. I am looking into specifying the character set when I open an ADODB connection but I haven't managed to get that working yet. If anyone has successfully done that then I would love to know

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[TheAceMan1]

MajP . . .

I forget to mention I put a stop point just to check the recordset return value [blue]rst!qName[/blue]. Sure enough ... when I hover the mouse over it I get "David [purple]AaAa"[/purple]. Should be "David [purple]Ă㥹"[/purple].

So in my case its the return value.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Ace Man,
This is just a rendering problem as I pointed out. Modify your code to show in access.

   Dim rst As DAO.Recordset
   Set rst = Me.RecordsetClone
   rst.MoveFirst
   Forms!SomForm.someTextbox.value= rst!qName
   Set rst = Nothing

It works fine for me, and displays properly.
As far as I know the VBE does not support unicode and it is converted to ANSI. So that is why the ASC values seem to be the closest "A". That was a bad test on my part because these do not have ASCII II values. I did not pay attention to the specific characters.

 

[TheAceMan1]

MajP . . .

With a [blue]break point[/blue] set here's a view of what [blue]form view[/blue] and the [blue]Visual Basic Editor[/blue] look like at that moment:

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PeteJohnston]

Guys

Success!

When VBA tries to use one of these special values and it doesn't recognise it then it converts it to its best guess (a rendering thing MajP?). My normal habit when trying to run a variable chunk of SQL is to set up a string then use CurrentDb.Execute to run the statement. It doesn't usually cause a problem and it lets you look at the statement in debug and check to see if it is correct. The result is that the string gets converted at that point. Even when you are looking at the value held in rst.Fields("Actual") VBA tries to translate it so it looks wrong. If you do the execute directly

db.Execute "Insert Into tblLinesInError (FileID, InvalidLine, LineText, Status) " & _
            "Select " & lFileID & ", LineID, F2, "& _
                     sStatus & " From tblImport " & _
            "Where F2 Like '*" & .Fields("Actual") & "*' " & _
              "And LineID Not In " & _
              "(Select InvalidLine From tblLinesInError Where FileID = " & lFileID & ")"

then SQL is happy and runs without VBA doing the conversion in between.

I've been smiling since around 9:300 this morning
Thanks for bouncing ideas around.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MajP]

With a break point set here's a view of what form view and the Visual Basic Editor look like at that moment:

Yes, I fully understand that and like I said, I totally agree. And that is a rendering issue of the vbe, it is not an issue with not returning the correct value in the recordset. The vbe does not handle unicode, but Access can. So it does not render in vbe but will if your print it to a text box. It is not a vba issue beacause as I demonstrated it will print fine, it is a vbe issue.

 

[TheAceMan1]

MajP . . .

I played with it a little more as I noticed the characters I chose [purple]ĀāĂă[/purple], were outside the normal ascii range (>255). Below are [blue]actual[/blue] values versus those [blue]returned[/blue] by VBE, both for in and out of range values:

[pre] Ā ā Ă ă
Actual 256 257 258 259 [green]'ascii >255[/green]
Returned 65 97 65 97 [green]'maybe some kind of lookup?[/green]

ü ý þ ÿ
Actual 252 253 254 255 [green]'ascii <=255[/green]
Returned 252 253 254 255 [green]'OK![/green][/pre]

I also noticed that I can't paste ascii characters >255 in VBE anywhere ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[MajP]

My understanding is that in order to render it in vbe is that it converts the unicode to ansi. Therefore the lower 256 render correctly since they have ascii values. above that I guess the conversion is the closest similar character since they do not have ascii values. But the simple test is read a recordset of greater than 255. Do a Debug.print and also send the values to a textbox on the screen. The immediate window will be wrong and the textbox will be correct. So obviously the recordset is returning the correct values, but they are rendered incorrectly int the immediate window and correctly on screen.

 

[TheAceMan1]

MajP . . .

Roger That! ...


See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[MajP]

From Litwin and Getz (Access Developers Handbook)
VBA stores all strings as unicode which is a 2byte representation of each character. This can represent 65,536 characters. Every character of every major language has been assigned a unicode character. ANSI uses 1byte and can only represent 256 different characters. When vba calls a function using a string, the strings are converted from unicode and placed in a temporary buffer. Then the pointers are fixed to point to the converted strings. When the function returns all strings are converted backed from ANSI to unicode before VBA returns control to you.
So I do not really understand all the implications, but I think this may explain why you can paste a unicode value in the VBE but not render it from debug.print, and why you can render in Access.

 

[PeteJohnston]

MajP
Going by the Litwin and Getz quote it seems that VBA converts the raw data to ANSI before placing it in a variable containing the SQL select command. End result is that the WHERE clause is incorrect. This must mean that passing the select string with rst.Fields("Actual") in the WHERE directly as the parameter for the db.Execute command bypasses this conversion? Seems a bit weird but since it returns the correct rows I guess that must be how it works.

Strange but true

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)