Invalid data on Left Join query

[Nunya]

Access 12.0
If I create a left join query that is based on a table and a query with an
expression in it the results of the second query contain data for every
record of the calculated field. Here is the setup
_____________________________________________________________________
Table Data
ID Field1 Field2
1 good
2 bad good
3 good bad
4 bad
5 good
6 bad
7 good
8 bad
9 good
10 bad
_____________________________________________________________________
Table header
ID Field1
1 use
2 use
3 use
4 use
5 use
6 not me
7 not me
8 not me
9 not me
10 not me
_____________________________________________________________________
Query1
SELECT data.ID, IIf(Right([field1] & [field2],2)="ad","failed","passed") AS
Expr1
FROM data
WHERE (((IIf(Right([field1] &
[field2],2)="ad","failed","passed"))="passed"));
produces
ID Expr1
1 passed
2 passed
5 passed
7 passed
9 passed
_____________________________________________________________________
Query2
SELECT header.ID, header.Field1, Query1.ID, Query1.Expr1
FROM header LEFT JOIN Query1 ON header.ID = Query1.ID;
produces
header.ID Field1 Query1.ID Expr1
1 use 1 passed
2 use 2 passed
3 use passed
4 use passed
5 use 5 passed
6 not me passed
7 not me 7 passed
8 not me passed
9 not me 9 passed
10 not me passed

Expr1 in query 2 should only have a value for ID 1,2,5,7,9 not every record.
These queries ran fine in 2003 but now that we have upgraded to 12.0 they
don't work. Any advice would be appreciated.

 

[KARL DEWEY]

I do not understand it but here is a fix ---
SELECT Header.ID, Header.Field1, Query1.ID, IIf([Query1].[ID] Is
Null,Null,[Expr1]) AS Expr2
FROM Header LEFT JOIN Query1 ON Header.ID = Query1.ID;

 

[Nunya]

I don't understand it either. I found some info in the KB about problems
with left outer joins but nothing close to this. I found that if I create a
table from query1 and use the made table I get the correct results so it must
be the expression causing the problems. How do you go about reporting a bug
to MS?

I do not understand it but here is a fix ---
SELECT Header.ID, Header.Field1, Query1.ID, IIf([Query1].[ID] Is
Null,Null,[Expr1]) AS Expr2
FROM Header LEFT JOIN Query1 ON Header.ID = Query1.ID;

--
KARL DEWEY
Build a little - Test a little

Access 12.0
If I create a left join query that is based on a table and a query with an
expression in it the results of the second query contain data for every
record of the calculated field. Here is the setup
_____________________________________________________________________
Table Data
ID Field1 Field2
1 good
2 bad good
3 good bad
4 bad
5 good
6 bad
7 good
8 bad
9 good
10 bad
_____________________________________________________________________
Table header
ID Field1
1 use
2 use
3 use
4 use
5 use
6 not me
7 not me
8 not me
9 not me
10 not me
_____________________________________________________________________
Query1
SELECT data.ID, IIf(Right([field1] & [field2],2)="ad","failed","passed") AS
Expr1
FROM data
WHERE (((IIf(Right([field1] &
[field2],2)="ad","failed","passed"))="passed"));
produces
ID Expr1
1 passed
2 passed
5 passed
7 passed
9 passed
_____________________________________________________________________
Query2
SELECT header.ID, header.Field1, Query1.ID, Query1.Expr1
FROM header LEFT JOIN Query1 ON header.ID = Query1.ID;
produces
header.ID Field1 Query1.ID Expr1
1 use 1 passed
2 use 2 passed
3 use passed
4 use passed
5 use 5 passed
6 not me passed
7 not me 7 passed
8 not me passed
9 not me 9 passed
10 not me passed

Expr1 in query 2 should only have a value for ID 1,2,5,7,9 not every record.
These queries ran fine in 2003 but now that we have upgraded to 12.0 they
don't work. Any advice would be appreciated.