Invalid Trailing Spaces Match

[Michael Keck - U of I]

I have a query with a subselect to determine if a table
has referential integrity issues with some validation
tables (There are no Foreign Keys in the Access Database
so they can enter data before the Validation Table is
updated)

One part of my subselect looks as follows:

OR NOT EXISTS( SELECT 'X' FROM SAT_SHRGRDE D WHERE
D.shrgrde_vpdi_code = A.shrgrde_vpdi_code AND
D.shrgrde_levl_code = A.bnr_pk1_cd AND
D.shrgrde_term_code_effective = A.bnr_start_term AND
D.SHRGRDE_CODE = A.BNR_GRDE_CODE)

The problem is that one of the values in the
D.SHRGRDE_CODE field = 'A' and the matching field in the
A.BNR_GRDE_CODE field = 'A ' (notice the trailing space),
but the query is saying these are a match.

I dynamically create this query as I have almost 3 dozen
tables to report validation errors.

Is this a Microsoft Access bug or do I not have some
system parameter set correctly.

Personally, a field with a trailing space should NEVER
equal a field with no trailing space.

 

[Gary Walter]

Hi Michael,

I created 2 test tables ("A" and "D"),
each with one text field ("fA" and "fD",
respectfully).

In Access 2000, I entered "A" in D.fD.

I couldn't enter "A " in A.fA,
it would always save record
as "A".

I ran a query on A.fA inserting
"A" & Chr(32).

I then ran the following query:

SELECT
A.fA,
D.fD,
Len([A].[fA]) AS LenA,
Len([D].[fD]) AS LenD
FROM A, D
WHERE (((A.fA)=[D].[fD]));

and got (to my surprise)

fA fD LenA LenD
A A 2 1

You're right...it just does not
seem correct that "A " should
equal "A"

But Access (any program) is full of these
"little things" that don't seem correct,
that we just have to work around.

No consolation, but what else can you
do but "pick yourself up, and dust
yourself off"...and congratulate
yourself for figuring it out.

Knowing what works vs what seems correct
is why we get paid so much.

Apologies if above sounds wiseacre...
not meaning to.

Gary Walter

 

[Gary Walter]

"Interestingly", VBA recognizes the difference

?"A "="A"
False

so it is only an "error" in the Jet,
kind of like how Jet ignores the
case of text fields (although VBA
can ignore case also).

Here's a few more things that don't "seem correct":

?"ABC"="abc"
True <--"doesn't seem correct"

?Int(59.33 * 100)
5932 <--"doesn't seem correct"
Workaround:
?Int(CDec(59.33) * 100)
5933

? 1.8 - 1.7
0.1 <-- okay
? 10.8 - 10.7
0.100000000000001 <--"doesn't seem correct"
? 100.8 - 100.7
9.99999999999943E-02 <--"doesn't seem correct"
? 1000.8 - 1000.7
9.99999999999091E-02 <--"doesn't seem correct"
? 10000.8 - 10000.7
9.99999999985448E-02 <--"doesn't seem correct"
Workaround:
? CDec(10000.8) - CDec(10000.7)
0.1

Hi Michael,

I created 2 test tables ("A" and "D"),
each with one text field ("fA" and "fD",
respectfully).

In Access 2000, I entered "A" in D.fD.

I couldn't enter "A " in A.fA,
it would always save record
as "A".

I ran a query on A.fA inserting
"A" & Chr(32).

I then ran the following query:

SELECT
A.fA,
D.fD,
Len([A].[fA]) AS LenA,
Len([D].[fD]) AS LenD
FROM A, D
WHERE (((A.fA)=[D].[fD]));

and got (to my surprise)

fA fD LenA LenD
A A 2 1

You're right...it just does not
seem correct that "A " should
equal "A"

But Access (any program) is full of these
"little things" that don't seem correct,
that we just have to work around.

No consolation, but what else can you
do but "pick yourself up, and dust
yourself off"...and congratulate
yourself for figuring it out.

Knowing what works vs what seems correct
is why we get paid so much.

Apologies if above sounds wiseacre...
not meaning to.

Gary Walter

I have a query with a subselect to determine if a table
has referential integrity issues with some validation
tables (There are no Foreign Keys in the Access Database
so they can enter data before the Validation Table is
updated)

One part of my subselect looks as follows:

OR NOT EXISTS( SELECT 'X' FROM SAT_SHRGRDE D WHERE
D.shrgrde_vpdi_code = A.shrgrde_vpdi_code AND
D.shrgrde_levl_code = A.bnr_pk1_cd AND
D.shrgrde_term_code_effective = A.bnr_start_term AND
D.SHRGRDE_CODE = A.BNR_GRDE_CODE)

The problem is that one of the values in the
D.SHRGRDE_CODE field = 'A' and the matching field in the
A.BNR_GRDE_CODE field = 'A ' (notice the trailing space),
but the query is saying these are a match.

I dynamically create this query as I have almost 3 dozen
tables to report validation errors.

Is this a Microsoft Access bug or do I not have some
system parameter set correctly.

Personally, a field with a trailing space should NEVER
equal a field with no trailing space.

 

[Gary Walter]

I forgot workaround:

SELECT
A.fA,
D.fD,
Len([A].[fA]) AS LenA,
Len([D].[fD]) AS LenD
FROM A INNER JOIN D
ON A.fA = D.fD
WHERE (((StrComp([A].[fA],[D].[fD],0))=0));


Note: initially joining on A.fA = D.fD
reduces the number of records that have
to run function on (especially more efficient
if fA and fD are indexed).