Left join query produces invalid results

[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.

 

[Nunya]

If you're really interested in helping me figur this out you should be able
to download a accdb with this problem here:
http://www.utteraccess.com/forums/s...1569875&page=0&view=collapsed&sb=5&o=&fpart=1