Query works differently on two PCs

[Cindy]

I have an Access 2003 database with a query that works fine on my PC
but does not work on another PC. My PC has older versions of Access
installed as well as 2003 - the other PC only has Access 2003.

The query is as follows:

SELECT tblOrganization .strOrgName, tblPerson.strNa meL,
tblPerson.strNa meF, tblPersonFCL_Co urses.lngIDPers on,
tblPersonFCL_Co urses.strHOTest , tblPersonFCL_Co urses.strCert,
tblPersonFCL_Co urses.ysnProces sed, tblStaffCert.ID Person,
tblStaffCert.st rCert
FROM (tblOrganizatio n INNER JOIN tblPerson ON
tblOrganization .IDorganization = tblPerson.lintF KOrganization) INNER
JOIN (tblPersonFCL_C ourses LEFT JOIN tblStaffCert ON
(tblPersonFCL_C ourses.strCert = tblStaffCert.st rCert) AND
(tblPersonFCL_C ourses.lngIDPer son = tblStaffCert.ID Person)) ON
tblPerson.IDPer son = tblPersonFCL_Co urses.lngIDPers on
WHERE (((tblPersonFCL _Courses.lngIDP erson) Not In (select lngIDPerson
from tblPersonFCL_Co urses where strCert="ML" and ysnProcessed =
false)) AND ((tblPersonFCL_ Courses.strCert )="ML") AND
((tblPersonFCL_ Courses.ysnProc essed)=True) AND
((tblStaffCert. IDPerson) Is Null));

When I run this query on my PC, it returns one record (which is
correct).

When we run the query on the problem PC - it returns a different
record, but it is in some sort of loop because we get hundreds of
records (all the same person - yet a different record from what
appears on the other PC with the same data files). We have to control
break to get out of this query.

I'm trying to resolve this remotely - I've got a fresh copy of both
the front end and back end that is on the problem PC... I can't
recreate any problems - all works great on mine.

We have tried:
compact/repair
new blank database for both front and back end, importing all objects
autoname correct is turned off
double-checked all library references and order of same (am using dao
3.6, excel and outlook libraries along with the standard vba, access
11, OLE automation and Active X Data objects)
We also tried converting it to Access 2000 format on both PC's - the
converted one works great on mine, does not work on the client PC -
returns the same error.

Any ideas of what else to check??
Thanks in advance.

[Allen Browne]

Since the query works on one computer an not on another (assuming identical
data), the first thing to look for would be a difference in versions.

Locate msjet40.dll (typically in windows\system3 2.)
Right-click it in the Windows Explorer, and choose Properties.
On the Version tab, what is the minor version?
It should be at least 4.0.8015.0.
Anything that starts with an 8 or 9 is fine. (It varies with your version of
Windows.) But if the minor version starts with 7 or less, download the
service pack from:
http://support.microsoft.com/kb/239114

If that does not solve the problem, and you are quite sure that the field
types and sizes match correctly, there is a potential issue with the yes/no
field in the WHERE clause and the outer join. Try:
AND (CBool(Nz(tblPe rsonFCL_Courses .ysnProcessed,F alse)) = True)

There is a whole class of bugs in Access based on the fact that JET cannot
handle nulls in yes/no fields, despite the fact that outer join queries
regularly yield nulls in boolean fields. Sometimes it crashes. Sometimes it
gives wrong results. Sometimes it gives nonsense errors as in this example:
http://allenbrowne.com/bug-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cindy" <ck*********@ya hoo.comwrote in message
news:11******** **************@ h3g2000cwc.goog legroups.com...

>I have an Access 2003 database with a query that works fine on my PC
but does not work on another PC. My PC has older versions of Access
installed as well as 2003 - the other PC only has Access 2003.

The query is as follows:

SELECT tblOrganization .strOrgName, tblPerson.strNa meL,
tblPerson.strNa meF, tblPersonFCL_Co urses.lngIDPers on,
tblPersonFCL_Co urses.strHOTest , tblPersonFCL_Co urses.strCert,
tblPersonFCL_Co urses.ysnProces sed, tblStaffCert.ID Person,
tblStaffCert.st rCert
FROM (tblOrganizatio n INNER JOIN tblPerson ON
tblOrganization .IDorganization = tblPerson.lintF KOrganization) INNER
JOIN (tblPersonFCL_C ourses LEFT JOIN tblStaffCert ON
(tblPersonFCL_C ourses.strCert = tblStaffCert.st rCert) AND
(tblPersonFCL_C ourses.lngIDPer son = tblStaffCert.ID Person)) ON
tblPerson.IDPer son = tblPersonFCL_Co urses.lngIDPers on
WHERE (((tblPersonFCL _Courses.lngIDP erson) Not In (select lngIDPerson
from tblPersonFCL_Co urses where strCert="ML" and ysnProcessed =
false)) AND ((tblPersonFCL_ Courses.strCert )="ML") AND
((tblPersonFCL_ Courses.ysnProc essed)=True) AND
((tblStaffCert. IDPerson) Is Null));

When I run this query on my PC, it returns one record (which is
correct).

When we run the query on the problem PC - it returns a different
record, but it is in some sort of loop because we get hundreds of
records (all the same person - yet a different record from what
appears on the other PC with the same data files). We have to control
break to get out of this query.

I'm trying to resolve this remotely - I've got a fresh copy of both
the front end and back end that is on the problem PC... I can't
recreate any problems - all works great on mine.

We have tried:
compact/repair
new blank database for both front and back end, importing all objects
autoname correct is turned off
double-checked all library references and order of same (am using dao
3.6, excel and outlook libraries along with the standard vba, access
11, OLE automation and Active X Data objects)
We also tried converting it to Access 2000 format on both PC's - the
converted one works great on mine, does not work on the client PC -
returns the same error.

Any ideas of what else to check??
Thanks in advance.

[Cindy]

Hi Allen -
The dll's on all machines are up to date. I modified the boolean
field as indicated, but the problem persists. I'm going to get around
it by using temp tables to denormalize - and in the meantime have
decided that I'm swearing off Access booleans!!! Many thanks for your
time.

The inconsistencies sure do seem to be getting worse though - sounds
like I've got to do some more planning ahead to make sure I'm not
going to hit this again!

Cindy