Query compare

  • Thread starter Thread starter Rover
  • Start date Start date
R

Rover

I have a query that looks up a code to find a description in the target
table. It is finding "ABC" and "ABc" codes as the same. OPTION COMPARE
TEXT - but I don't know how to change that setting for the database to
always do BINARY compares.

TIA
 
Hi,


SQL is not aware of the VBA options. Just imagine that SQL works outside
Access, outside VBA. So, SQL is independent of the VBA settings.

Now, when you are using Jet within Access, you can include VBA-FUNCTIONS
in the SQL statement. StrComp( ) is such a function, and it can be used to
compare lexicographically two strings, returning -1, 0, or +1 ( before,
equal, after) accordingly to the optional parameter, 0 for a binary
comparison, 1 for a text comparison, and 2 for a database comparison.


? StrComp( "aAa", "AAa", 1033)
0

? StrComp("aAa", "AAa", 0)
1


REMEMBER that 0 means the strings are equal! 1 means that the first
string is to appear "after" the second string, if they are to be ordered.



Hoping it may help,
Vanderghast, Access MVP
 
Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
 
Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant string.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks, that did it

Michel said:
Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant string.



Hoping it may help,
Vanderghast, Access MVP


Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
 
0=StrComp( table1.Field1, table2.field2, 0 ) works fine for the table to
table comparison but why does my Query B which uses
((ppbenf.ppcode_benf)="E1CA") work as a binary compare? hmmmm...

Michel said:
Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant string.



Hoping it may help,
Vanderghast, Access MVP


Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
 
Hi,


Should not. In the immediate (debug) window:

=====================================================
CurrentDb.Execute "CREATE TABLE testing (f1 varchar(50)) "
CurrentDb.Execute "INSERT INTO testing(f1) VALUES('AaAa') "
CurrentDb.Execute "INSERT INTO testing(f1) VALUES('AAAA') "
? CurrentDb.OpenRecordset("SELECT COUNT(*) FROM testing WHERE
f1='aaaa'").Fields(0).Value
2
======================================================


we got f1='aaaa' with two matches, while a binary comparison would have
supplied 0.


Same thing if I use a module:

=======================================================
Option Compare Binary '<<<<<<<<<<
Option Explicit

Public Sub TestingItInAModule()
Debug.Print CurrentDb.OpenRecordset("SELECT COUNT(*) FROM testing WHERE
f1='aaaa'").Fields(0).Value
End Sub
=========================================================


a 2 is returned in the immediate window.


Vanderghast, Access MVP


Rover said:
0=StrComp( table1.Field1, table2.field2, 0 ) works fine for the table to
table comparison but why does my Query B which uses
((ppbenf.ppcode_benf)="E1CA") work as a binary compare? hmmmm...

Michel said:
Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant string.



Hoping it may help,
Vanderghast, Access MVP


Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
Click to expand...
 
That's all well and good but, if I run this query:
================
SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="9711A") AND
((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
=============
NOT in a VBA window, but in Access, I only get one record with code
EICA. If I chang the ppcode to (ppbenf.ppcode_benf) like "E1*A") I get
two records. hmmmm...

Michel said:
Hi,


Should not. In the immediate (debug) window:

=====================================================
CurrentDb.Execute "CREATE TABLE testing (f1 varchar(50)) "
CurrentDb.Execute "INSERT INTO testing(f1) VALUES('AaAa') "
CurrentDb.Execute "INSERT INTO testing(f1) VALUES('AAAA') "
? CurrentDb.OpenRecordset("SELECT COUNT(*) FROM testing WHERE
f1='aaaa'").Fields(0).Value
2
======================================================


we got f1='aaaa' with two matches, while a binary comparison would have
supplied 0.


Same thing if I use a module:

=======================================================
Option Compare Binary '<<<<<<<<<<
Option Explicit

Public Sub TestingItInAModule()
Debug.Print CurrentDb.OpenRecordset("SELECT COUNT(*) FROM testing WHERE
f1='aaaa'").Fields(0).Value
End Sub
=========================================================


a 2 is returned in the immediate window.


Vanderghast, Access MVP


0=StrComp( table1.Field1, table2.field2, 0 ) works fine for the table to
table comparison but why does my Query B which uses
((ppbenf.ppcode_benf)="E1CA") work as a binary compare? hmmmm...

Michel Walsh wrote:

Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant
Click to expand...
string.
Hoping it may help,
Vanderghast, Access MVP




Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc

FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =

ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf

FROM ppbenf

WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
Click to expand...
 
I think you've found it... This table is really a Cobol table and is
accessed through Relitivity using an ODBC connection. That, I guess
would explain it. Thanks.
Jim

Gary said:
Hi Rover,

Just some thoughts w/o
reading previous posts....

"Where are these tables?"

The Jet engine is case-insensitive,
so I can only wonder if "ppbenf"
is a linked Paradox table (or in some
other non-Access db which IS case
sensitive).

Or...what types of fields are these.
Can we assume the text fields in both tables are
type TEXT (and not BINARY/Unicode)?

HOWTO: Perform a Case-Sensitive JOIN Through Microsoft Jet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q244693

Probably not much help, but had to ask.

Gary Walter
Click to expand...

0=StrComp( table1.Field1, table2.field2, 0 ) works fine for the table to
table comparison but why does my Query B which uses
((ppbenf.ppcode_benf)="E1CA") work as a binary compare? hmmmm...

Michel said:
Hi,


You must not use

table1.field1 = table2.field2
but

0=StrComp( table1.Field1, table2.field2, 0 )


to binary compare strings stored in table(s), or


0=StrComp( table1.Field1, "aAaC", 0)


for binary comparison between a string in a table with a constant string.



Hoping it may help,
Vanderghast, Access MVP


Actually, no that doesn't help a lot. Maybe I misstated the real
problem. Here is a further definition of the problem:

When I run Query A it:
1) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
2) and more importantly, it gets equal compare on codes "E1CA" and
"E1cA" whereas Query B doesn't. I have verified that tbl_detail_Bene
only contains "E1CA"

I need to resolve both issues but item 2 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top