Access XP, .mdb saved in 2K format

J

Jim Carlock

Hello,

I'm running into a problem where I have a field
(text of size 2 in the design view)
(does not initialize to any set value)

The .mdb is saved in an access 2k format, I'm using Access XP.

I started running into some problems where the data retrieved
didn't seem correct through DAO.

I compacted the file (Tools, Database, Compact and Repair).
And the problems are still occuring.

Field name: P1

I proceeded to configure Query1 as a Select query. The fields
I'm reading are all of size 2, unindexed. In the criteria portion,
I've placed " " (a string of two spaces). That is the only criteria
I want to find.

I've also told it to show the length of the field as another field.
Length: Len([P1])

The results returned: 36 records found.

Inside the fields found, "" (an empty string).
Length: returns 0.

I'm a little puzzled right at this moment. The only fields I'm
asking it to return are the calculated field getting the length
of P1 and P1 itself if P1 = " " (two spaces). The results
are NOT what I expect.

I changed the query to an update query and for the update
field I told it change it to an empty string (""). Nothing
changed in running the select query. The update didn't
take effect.

I was thinking, Okay those fields contain Chr$(0), but
they should've updated.

I changed the criteria to Chr$(0) and changed it back to
a "Select" type of query. The same results are returned.

I changed the criteria to " " (two spaces), and the same
results are returned.

1) How do I update such fields to be represented as an
empty string instead of Chr$(0)?
2) Why is " " (the two spaces) as the criteria returning True?

Any comments and/or suggestions will be greatly
appreciated.
 
J

John Vinson

Hello,

I'm running into a problem where I have a field
(text of size 2 in the design view)
(does not initialize to any set value)

The .mdb is saved in an access 2k format, I'm using Access XP.

I started running into some problems where the data retrieved
didn't seem correct through DAO.

I compacted the file (Tools, Database, Compact and Repair).
And the problems are still occuring.

Field name: P1

I proceeded to configure Query1 as a Select query. The fields
I'm reading are all of size 2, unindexed. In the criteria portion,
I've placed " " (a string of two spaces). That is the only criteria
I want to find.
Click to expand...

Well, you won't find it. Access trims trailing blanks in Text fields.
If you enter "" or " " or " " into a field, it will store the field
as NULL, unless you have set the Allow Zero Length property of the
field to true, in which case it will store a zero length string.
I've also told it to show the length of the field as another field.
Length: Len([P1])

The results returned: 36 records found.

Inside the fields found, "" (an empty string).
Length: returns 0.

I'm a little puzzled right at this moment. The only fields I'm
asking it to return are the calculated field getting the length
of P1 and P1 itself if P1 = " " (two spaces). The results
are NOT what I expect.
Click to expand...

They're what I expect.
I changed the query to an update query and for the update
field I told it change it to an empty string (""). Nothing
changed in running the select query. The update didn't
take effect.

I was thinking, Okay those fields contain Chr$(0), but
they should've updated.

I changed the criteria to Chr$(0) and changed it back to
a "Select" type of query. The same results are returned.

I changed the criteria to " " (two spaces), and the same
results are returned.
Click to expand...

Try a criterion of IS NULL.
1) How do I update such fields to be represented as an
empty string instead of Chr$(0)?
Click to expand...

It's not storing Chr$(0) - it's EMPTY, it has *nothing* in it.

John W. Vinson[MVP]
 
J

Jim Carlock

John, thanks for the response. I'm totally puzzled by it though.

1) If I set the criterion up to be:
Chr$(0)
the recordset returned is the same as if:
2) I set the criterion up to be:
" "
That's two blank spaces.

What might be causing two different queries with two different
criteria to return the same recordset?

--
Jim Carlock
Post replies to newsgroup.

Hello,

I'm running into a problem where I have a field
(text of size 2 in the design view)
(does not initialize to any set value)

The .mdb is saved in an access 2k format, I'm using Access XP.

I started running into some problems where the data retrieved
didn't seem correct through DAO.

I compacted the file (Tools, Database, Compact and Repair).
And the problems are still occuring.

Field name: P1

I proceeded to configure Query1 as a Select query. The fields
I'm reading are all of size 2, unindexed. In the criteria portion,
I've placed " " (a string of two spaces). That is the only criteria
I want to find.
Click to expand...

Well, you won't find it. Access trims trailing blanks in Text fields.
If you enter "" or " " or " " into a field, it will store the field
as NULL, unless you have set the Allow Zero Length property of the
field to true, in which case it will store a zero length string.
I've also told it to show the length of the field as another field.
Length: Len([P1])

The results returned: 36 records found.

Inside the fields found, "" (an empty string).
Length: returns 0.

I'm a little puzzled right at this moment. The only fields I'm
asking it to return are the calculated field getting the length
of P1 and P1 itself if P1 = " " (two spaces). The results
are NOT what I expect.
Click to expand...

They're what I expect.
I changed the query to an update query and for the update
field I told it change it to an empty string (""). Nothing
changed in running the select query. The update didn't
take effect.

I was thinking, Okay those fields contain Chr$(0), but
they should've updated.

I changed the criteria to Chr$(0) and changed it back to
a "Select" type of query. The same results are returned.

I changed the criteria to " " (two spaces), and the same
results are returned.
Click to expand...

Try a criterion of IS NULL.
1) How do I update such fields to be represented as an
empty string instead of Chr$(0)?
Click to expand...

It's not storing Chr$(0) - it's EMPTY, it has *nothing* in it.

John W. Vinson[MVP]
 
J

John Vinson

John, thanks for the response. I'm totally puzzled by it though.

1) If I set the criterion up to be:
Chr$(0)
the recordset returned is the same as if:
2) I set the criterion up to be:
" "
That's two blank spaces.

What might be causing two different queries with two different
criteria to return the same recordset?
Click to expand...

I'm perplexed too!

Please post the SQL view of this query. Also check the table
definition for this field - what are the values of the Allow Zero
Length String and Required fields?

John W. Vinson[MVP]
 
J

Jim Carlock

Additionally, Chr$(0) was stored in some of the fields. The field
is used to hold a Plant number. The contents are set to a size of
2. The field is NOT required to contain anything. The program
is using DAO to read and write information to the Access .mdb.
The .mdb was originally imported from an Access 1.0 format,
so everything originally came from Access 1.0.

So perhaps Chr$(0) was in some of the fields. And I started
going through deleting the contents of the fields, and I thought
I'd check out some other things as well... and that's when
things started getting wierd. I changed the criteria of what I
was searching for to Chr$(0) because I was originally looking
for space characters Chr$(32).

I INCORRECTLY stated that they are returning the same
recordsets. Some of the records are the same, some are not.
The first 10 records I looked at for the queries I set up DO
return the same record numbers, after which they start to
differ...

1 7 7
2 23 23
3 30 30
4 31 31
5 33 33
6 43 43
7 44 44
8 45 45
9 46 46
10 47 47
11 50 48

Now I'm seeing some of the fields contain two space characters
(chr$(32)), and some of them contain two Chr$(0).

Furthermore, I converted the file from Access 2K to Access 2002
and the results seem to be the same.

I don't think I have Access 97 laying around conveniently, so I'm
going to forget about messing with converting the file to an Access
97 format. I probably can set up a subset of the table with the
problems somewhere if anyone wants to take a look at it. It's got
me baffled and I was hoping someone out there might have run
across it and have a quick answer... so I'm still looking for that.

Thanks much.

--
Jim Carlock
Post replies to newsgroup.

:
John, thanks for the response. I'm totally puzzled by it though.

1) If I set the criterion up to be:
Chr$(0)
the recordset returned is the same as if:
2) I set the criterion up to be:
" "
That's two blank spaces.

What might be causing two different queries with two different
criteria to return the same recordset?

--
Jim Carlock
Post replies to newsgroup.

:
Hello,

I'm running into a problem where I have a field
(text of size 2 in the design view)
(does not initialize to any set value)

The .mdb is saved in an access 2k format, I'm using Access XP.

I started running into some problems where the data retrieved
didn't seem correct through DAO.

I compacted the file (Tools, Database, Compact and Repair).
And the problems are still occuring.

Field name: P1

I proceeded to configure Query1 as a Select query. The fields
I'm reading are all of size 2, unindexed. In the criteria portion,
I've placed " " (a string of two spaces). That is the only criteria
I want to find.
Click to expand...

Well, you won't find it. Access trims trailing blanks in Text fields.
If you enter "" or " " or " " into a field, it will store the field
as NULL, unless you have set the Allow Zero Length property of the
field to true, in which case it will store a zero length string.
I've also told it to show the length of the field as another field.
Length: Len([P1])

The results returned: 36 records found.

Inside the fields found, "" (an empty string).
Length: returns 0.

I'm a little puzzled right at this moment. The only fields I'm
asking it to return are the calculated field getting the length
of P1 and P1 itself if P1 = " " (two spaces). The results
are NOT what I expect.
Click to expand...

They're what I expect.
I changed the query to an update query and for the update
field I told it change it to an empty string (""). Nothing
changed in running the select query. The update didn't
take effect.

I was thinking, Okay those fields contain Chr$(0), but
they should've updated.

I changed the criteria to Chr$(0) and changed it back to
a "Select" type of query. The same results are returned.

I changed the criteria to " " (two spaces), and the same
results are returned.
Click to expand...

Try a criterion of IS NULL.
1) How do I update such fields to be represented as an
empty string instead of Chr$(0)?
Click to expand...

It's not storing Chr$(0) - it's EMPTY, it has *nothing* in it.

John W. Vinson[MVP]
 
J

Jim Carlock

<g> Okay, using the links to the Access 1.0 database...

Using the link to the Access Version 1.0 table:

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(32) & Chr$(32)));

returns record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(0) & Chr$(0)));

returns the record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

returns the record with MatlNum: 326

So I set up the following query to look at what's in record 326...

SELECT linkMaterials.MatlNum, _
linkMaterials.Plant0, _
Len([MatlNum]) AS MNLength, _
Hex$(Asc(Left$([MatlNum],1))) & " " & _
Hex$(Asc(Mid$([MatlNum],1,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],2,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],3,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],4,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],5,1))) AS MNHex, _
Len([Plant0]) AS P0Length, _
Hex$(Asc(Left$([Plant0],1))) & " " & _
Hex$(Asc(Right$([Plant0],1))) AS P0Hex
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

That returned the following:
MatlNum 326
Plant0
MNLength 6
MNHex 33 33 32 36 20 20
P0Length 2
P0Hex 0 0

Bummer that Chr$(9) is NOT handled by Outlook Express. <g>

The record at MatlNum 326 IS unique. There is ONLY ONE
material number 326. So that means the problem existed in
Access Version 1.0. I messed around for a moment trying to
duplicate it INSIDE of ACCESS 2002 (XP) but I gave up on
that. The problem exists with records imported from Access 1.0
even though it's in an Access XP (2002) .mdb configured as
either Access 2000 or Access 2002.

It all started while I was importing material numbers into a
listbox. I started changing a couple things here and there
and the code wasn't looking all that great and humpty
dumpty, I kept wondering why did they check to see if the
field had Chr$(0) in them. So I took that check out and I
started running into problems. I was puzzled and I posted
the first message here.

I started going through thinking it was something I did, thinking
there can't be but one or two records with a problem. While
browsing through the data in the actual table, I noticed that two
characters would be highlighted in that particular field, and I
thought, well... maybe I'll just search for fields with two spaces,
" ".

And because of the Chr$(0) problem in the application, I decided
to query the records that were configured as Chr$(0) as well.
More than one record popped up. And the first 10 were the same
records. <g>

So I thought they returned the same records. But going along the
lines that I should NOT assume anything anymore...

Well here we are. <g> I'm usually one of the first people to jump
out and say NEVER ASSUME anything... So I'll say it NOW...
NEVER ASSUME !!! (especially when dealing with Microsoft
software). <beg> I couldn't resist.

--
Jim Carlock
Post replies to newsgroup.

:
John, thanks for the response. I'm totally puzzled by it though.

1) If I set the criterion up to be:
Chr$(0)
the recordset returned is the same as if:
2) I set the criterion up to be:
" "
That's two blank spaces.

What might be causing two different queries with two different
criteria to return the same recordset?
Click to expand...

I'm perplexed too!

Please post the SQL view of this query. Also check the table
definition for this field - what are the values of the Allow Zero
Length String and Required fields?

John W. Vinson[MVP]
 
D

Dirk Goldgar

Jim Carlock said:
<g> Okay, using the links to the Access 1.0 database...

Using the link to the Access Version 1.0 table:

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(32) & Chr$(32)));

returns record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(0) & Chr$(0)));

returns the record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

returns the record with MatlNum: 326

So I set up the following query to look at what's in record 326...

SELECT linkMaterials.MatlNum, _
linkMaterials.Plant0, _
Len([MatlNum]) AS MNLength, _
Hex$(Asc(Left$([MatlNum],1))) & " " & _
Hex$(Asc(Mid$([MatlNum],1,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],2,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],3,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],4,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],5,1))) AS MNHex, _
Len([Plant0]) AS P0Length, _
Hex$(Asc(Left$([Plant0],1))) & " " & _
Hex$(Asc(Right$([Plant0],1))) AS P0Hex
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

That returned the following:
MatlNum 326
Plant0
MNLength 6
MNHex 33 33 32 36 20 20
P0Length 2
P0Hex 0 0

Bummer that Chr$(9) is NOT handled by Outlook Express. <g>

The record at MatlNum 326 IS unique. There is ONLY ONE
material number 326. So that means the problem existed in
Access Version 1.0. I messed around for a moment trying to
duplicate it INSIDE of ACCESS 2002 (XP) but I gave up on
that. The problem exists with records imported from Access 1.0
even though it's in an Access XP (2002) .mdb configured as
either Access 2000 or Access 2002.

It all started while I was importing material numbers into a
listbox. I started changing a couple things here and there
and the code wasn't looking all that great and humpty
dumpty, I kept wondering why did they check to see if the
field had Chr$(0) in them. So I took that check out and I
started running into problems. I was puzzled and I posted
the first message here.

I started going through thinking it was something I did, thinking
there can't be but one or two records with a problem. While
browsing through the data in the actual table, I noticed that two
characters would be highlighted in that particular field, and I
thought, well... maybe I'll just search for fields with two spaces,
" ".

And because of the Chr$(0) problem in the application, I decided
to query the records that were configured as Chr$(0) as well.
More than one record popped up. And the first 10 were the same
records. <g>

So I thought they returned the same records. But going along the
lines that I should NOT assume anything anymore...

Well here we are. <g> I'm usually one of the first people to jump
out and say NEVER ASSUME anything... So I'll say it NOW...
NEVER ASSUME !!! (especially when dealing with Microsoft
software). <beg> I couldn't resist.
Click to expand...

I think you're running into the simple fact that the Jet database
compares text values loosely. I'm sure you already know that text
comparisons are not case-sensitive. For example, if you have a record
with "a" in a field Foo, and you query with {SELECT * FROM MyTable WHERE
Foo="A"}, you'll get that record with Foo="a" even though you asked for
Foo="A". You may not be aware that Jet also equates most accented forms
of letters. So, as far as Jet is concerned, "a" = "à" = "á" = "â" = "ã"
= "ä" = "å".

We also know that Jet considers all blank fields to be equal to each
other, no matter how many blanks there are. So, for Jet, "" = " " = "
" = " ". I believe your tests demonstrate that Jet considers the
null character, Chr$(0), to be equivalent to a blank. You can test this
directly, if you like by running a query like this:

SELECT * FROM AnyTable WHERE Chr(0)=" ";

All records will be returned.

If you need to actually locate those records where a text field holds
Chr(0), you can create a calculated field that applies the Asc()
function to the field, and apply a numeric criterion to that; e.g.,

SELECT * FROM MyTable WHERE Asc(Foo) = 0;
 
J

Jim Carlock

Excellent explanation. Thanks for the extra comments about the
foreign characters as well. Re-learn something every once in a
while or learn something new. Whichever, however, whatever.

Your explanation is text-book quality. Should be put on a
billboard or street sign on the way into Redmond,
Washington... (except it didn't turn out to be 100% true).

I imagine if a table of characters considered equal would be
easily configured inside of an Access table, using a query
searching for "A" would return all the equivalents. Definitely
worth having around. All too easy too.

....

And the results of creating a table with characters 0 to 255...

Querying that table returns "A" and "a" when testing "a", and
returns the same when testing "A". However, codes 192 to
197 are plainly visible as those funny foreign "A" s and 224 to
229 are the lowercase foreign "a" s, none of which turned up
as being equal in the query.

Your statement:
So, as far as Jet is concerned,
"a" = "à" = "á" = "â" = "ã" = "ä" = "å"
Click to expand...

appears to be FALSE. The .mdb is a newly created .mdb saved
in an Access 2000 format (Access 2002 application).

I used a Reference to DAO 3.6 to build the table...

Function BuildSymbols() As Long
Dim db As DAO.Database, rs As DAO.Recordset
Set db = DAO.OpenDatabase(Access.CodeProject.Path & "\equality.mdb")
Set rs = db.OpenRecordset("tSymbols")
For i = 1 to 255
rs.Append
rs.Fields("Hex").Value = Hex$(i)
rs.Fields("Code").Value = i
rs.Fields("Symbol").Value = Chr$(i)
rs.Update
Next i
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

The query SQL I ran (everything was done inside of Access):
SELECT tSymbols.id, tSymbols.Code, tSymbols.Hex, tSymbols.Symbol
FROM tSymbols
WHERE (((tSymbols.Symbol)="a"));

The records returned as equal: "A" and "a" as follows...
Symbol: A, Hex: 41, Code: 65, id: 66
Symbol: a, Hex: 61, Code: 97, id: 98

The following symbols ( I don't know if OE is going to handle
these properly) were NOT equal:

Codes: 192 to 198: À, Á, Â, Ã, Ä, Å, Æ
Codes: 225 to 230: à, á, â, ã, ä, å, æ

Chr$(0) and Chr$(32) appear as equal.

HTH.

--
Jim Carlock
Please post replies to newsgroup.

:
Jim Carlock said:
<g> Okay, using the links to the Access 1.0 database...

Using the link to the Access Version 1.0 table:

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(32) & Chr$(32)));

returns record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=Chr$(0) & Chr$(0)));

returns the record with MatlNum: 326

SELECT linkMaterials.MatlNum, linkMaterials.Plant0
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

returns the record with MatlNum: 326

So I set up the following query to look at what's in record 326...

SELECT linkMaterials.MatlNum, _
linkMaterials.Plant0, _
Len([MatlNum]) AS MNLength, _
Hex$(Asc(Left$([MatlNum],1))) & " " & _
Hex$(Asc(Mid$([MatlNum],1,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],2,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],3,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],4,1))) & " " & _
Hex$(Asc(Mid$([MatlNum],5,1))) AS MNHex, _
Len([Plant0]) AS P0Length, _
Hex$(Asc(Left$([Plant0],1))) & " " & _
Hex$(Asc(Right$([Plant0],1))) AS P0Hex
FROM linkMaterials
WHERE (((linkMaterials.Plant0)=" "));

That returned the following:
MatlNum 326
Plant0
MNLength 6
MNHex 33 33 32 36 20 20
P0Length 2
P0Hex 0 0

Bummer that Chr$(9) is NOT handled by Outlook Express. <g>

The record at MatlNum 326 IS unique. There is ONLY ONE
material number 326. So that means the problem existed in
Access Version 1.0. I messed around for a moment trying to
duplicate it INSIDE of ACCESS 2002 (XP) but I gave up on
that. The problem exists with records imported from Access 1.0
even though it's in an Access XP (2002) .mdb configured as
either Access 2000 or Access 2002.

It all started while I was importing material numbers into a
listbox. I started changing a couple things here and there
and the code wasn't looking all that great and humpty
dumpty, I kept wondering why did they check to see if the
field had Chr$(0) in them. So I took that check out and I
started running into problems. I was puzzled and I posted
the first message here.

I started going through thinking it was something I did, thinking
there can't be but one or two records with a problem. While
browsing through the data in the actual table, I noticed that two
characters would be highlighted in that particular field, and I
thought, well... maybe I'll just search for fields with two spaces,
" ".

And because of the Chr$(0) problem in the application, I decided
to query the records that were configured as Chr$(0) as well.
More than one record popped up. And the first 10 were the same
records. <g>

So I thought they returned the same records. But going along the
lines that I should NOT assume anything anymore...

Well here we are. <g> I'm usually one of the first people to jump
out and say NEVER ASSUME anything... So I'll say it NOW...
NEVER ASSUME !!! (especially when dealing with Microsoft
software). <beg> I couldn't resist.
Click to expand...

I think you're running into the simple fact that the Jet database
compares text values loosely. I'm sure you already know that text
comparisons are not case-sensitive. For example, if you have a record
with "a" in a field Foo, and you query with {SELECT * FROM MyTable WHERE
Foo="A"}, you'll get that record with Foo="a" even though you asked for
Foo="A". You may not be aware that Jet also equates most accented forms
of letters. So, as far as Jet is concerned, "a" = "à" = "á" = "â" = "ã"
= "ä" = "å".

We also know that Jet considers all blank fields to be equal to each
other, no matter how many blanks there are. So, for Jet, "" = " " = "
" = " ". I believe your tests demonstrate that Jet considers the
null character, Chr$(0), to be equivalent to a blank. You can test this
directly, if you like by running a query like this:

SELECT * FROM AnyTable WHERE Chr(0)=" ";

All records will be returned.

If you need to actually locate those records where a text field holds
Chr(0), you can create a calculated field that applies the Asc()
function to the field, and apply a numeric criterion to that; e.g.,

SELECT * FROM MyTable WHERE Asc(Foo) = 0;
 
D

Dirk Goldgar

Jim Carlock said:
Excellent explanation. Thanks for the extra comments about the
foreign characters as well. Re-learn something every once in a
while or learn something new. Whichever, however, whatever.

Your explanation is text-book quality. Should be put on a
billboard or street sign on the way into Redmond,
Washington... (except it didn't turn out to be 100% true).
Oops.

Your statement:
So, as far as Jet is concerned,
"a" = "à" = "á" = "â" = "ã" = "ä" = "å"
Click to expand...

appears to be FALSE. The .mdb is a newly created .mdb saved
in an Access 2000 format (Access 2002 application). [...]

The query SQL I ran (everything was done inside of Access):
SELECT tSymbols.id, tSymbols.Code, tSymbols.Hex, tSymbols.Symbol
FROM tSymbols
WHERE (((tSymbols.Symbol)="a"));

The records returned as equal: "A" and "a" as follows...
Symbol: A, Hex: 41, Code: 65, id: 66
Symbol: a, Hex: 61, Code: 97, id: 98

The following symbols ( I don't know if OE is going to handle
these properly) were NOT equal:

Codes: 192 to 198: À, Á, Â, Ã, Ä, Å, Æ
Codes: 225 to 230: à, á, â, ã, ä, å, æ
Click to expand...

Obviously, I was wrong. I could swear I'd read that the accented forms
of a character were all equal in text comparisons, but I can't find any
such reference now, and your test plainly shows otherwise. I tested the
space/Chr(0) equivalence, but not the accented characters. Please
accept my apology for the misinformation.
Chr$(0) and Chr$(32) appear as equal.
Click to expand...

That's something, anyway.
 
J

Jim Carlock

I ran another test I forgot to post:

Search for symbol: Á
Returns two codes, upper and lower case symbols:
Code Hex Symbol
193 C1 Á
226 E1 á

So the same things that apply to the 26 alphabetic characters,
about upper and lower case characters, applies to those extra
symbols with diacritical marks. I can see where your statement
could have two interpretations and it's easy to misinterpret what
you said, whether what you said is what you meant or not.

I did not test that to try to disprove what you said, I just wanted
to see what was actually returned and see if there were any
characters you might have missed.

I knew about the upper and lower case, but didn't know about
the Chr$(0) and Chr$(32) being equal. Good stuff to know!

Thanks much!

--
Jim Carlock
Please post replies to newsgroup.

:
Jim Carlock said:
Excellent explanation. Thanks for the extra comments about the
foreign characters as well. Re-learn something every once in a
while or learn something new. Whichever, however, whatever.

Your explanation is text-book quality. Should be put on a
billboard or street sign on the way into Redmond,
Washington... (except it didn't turn out to be 100% true).
Oops.

Your statement:
So, as far as Jet is concerned,
"a" = "à" = "á" = "â" = "ã" = "ä" = "å"
Click to expand...

appears to be FALSE. The .mdb is a newly created .mdb saved
in an Access 2000 format (Access 2002 application). [...]

The query SQL I ran (everything was done inside of Access):
SELECT tSymbols.id, tSymbols.Code, tSymbols.Hex, tSymbols.Symbol
FROM tSymbols
WHERE (((tSymbols.Symbol)="a"));

The records returned as equal: "A" and "a" as follows...
Symbol: A, Hex: 41, Code: 65, id: 66
Symbol: a, Hex: 61, Code: 97, id: 98

The following symbols ( I don't know if OE is going to handle
these properly) were NOT equal:

Codes: 192 to 198: À, Á, Â, Ã, Ä, Å, Æ
Codes: 225 to 230: à, á, â, ã, ä, å, æ
Click to expand...

Obviously, I was wrong. I could swear I'd read that the accented forms
of a character were all equal in text comparisons, but I can't find any
such reference now, and your test plainly shows otherwise. I tested the
space/Chr(0) equivalence, but not the accented characters. Please
accept my apology for the misinformation.
Chr$(0) and Chr$(32) appear as equal.
Click to expand...

That's something, anyway.
 
D

Dirk Goldgar

Jim Carlock said:
I can see where your statement
could have two interpretations and it's easy to misinterpret what
you said, whether what you said is what you meant or not.
Click to expand...

There's no need to be so polite about it. I was just wrong, and I'm
glad you tested it and set me straight.

Thanks.
 
Top